Is there an easy way to see the code used to create a view using the PostgreSQL command-line client?
Something like the SHOW CREATE VIEW
from MySQL.
This question is related to
postgresql
view
definition
GoodNews from v.9.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
select definition from pg_views where viewname = 'my_view'
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
If you want an ANSI SQL-92 version:
select view_definition from information_schema.views where table_name = 'view_name';
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 rewrited version of your original DDL.
The rewited version may or not be the same as your originl 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 rewrited version of your view definition.
Not all views are rewrited but if you use sub-select or joins probably your views will be rewrited.
Source: Stackoverflow.com