How to see the CREATE VIEW code for a view in PostgreSQL?

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.

Solution 1:

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).

Solution 2:

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

Solution 3:

select definition from pg_views where viewname = 'my_view'

Solution 4:

If you want an ANSI SQL-92 version:

select view_definition from information_schema.views where table_name = 'view_name';

Solution 5:

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