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

Question

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

User · Answer

select definition from pg views where viewname    my view

User · Answer

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

User · Answer

If you want an ANSI SQL-92 version   select view definition from information schema views where table name    view name

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to postgresql

Examples related to view

Examples related to definition