Menu
The PostgreSQL views are created using the CREATE VIEW statement. The PostgreSQL views can be created from a single table, multiple tables, or another view. CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
To create a PostgreSQL view, we use the CREATE VIEW statement. Here is the syntax for this statement: CREATE [OR REPLACE] VIEW view-name AS SELECT column(s) FROM table(s) [WHERE condition(s)]; The OR REPLACE parameter will replace the view if it already exists.
SELECT column_names FROM view_name ; A view column name list must be specified for a recursive view. The name (optionally schema-qualified) of a view to be created. An optional list of names to be used for columns of the view.
Kept having to return here to look up pg_get_viewdef (how to remember that!!), so searched for a more memorable command... and got it:
\d+ viewname
You can see similar sorts of commands by typing \? at the pgsql command line.
Bonus tip: The emacs command sql-postgres makes pgsql a lot more pleasant (edit, copy, paste, command history).
select pg_get_viewdef('viewname', true)
A list of all those functions is available in the manual:
http://www.postgresql.org/docs/current/static/functions-info.html
select definition from pg_views where viewname = 'my_view'
If you want an ANSI SQL-92 version:
select view_definition from information_schema.views where table_name = 'view_name';
Good news from v9.6 and above. View editing are now native from psql. Just invoke \ev command. View definitions will show in your configured editor.
julian@assange=# \ev your_view_names
Bonus. Some useful command to interact with query buffer.
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
These is a little thing to point out.
Using the function pg_get_viewdef or pg_views or information_schema.views you will always get a rewritten version of your original DDL.
The rewritten version may or not be the same as your original DDL script.
If the Rule Manager rewrite your view definition your original DLL will be lost and you will able to read the only the rewritten version of your view definition.
Not all views are rewritten but if you use sub-select or joins probably your views will be rewritten.
In the command line client psql you can use following command:
\sv <VIEWNAME>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With