[postgresql] 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.

This question is related to postgresql view definition

The answer is


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.


Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to view

Empty brackets '[]' appearing when using .where SwiftUI - How do I change the background color of a View? Drop view if exists Difference between View and ViewGroup in Android How to make a view with rounded corners? How to remove all subviews of a view in Swift? How to get a view table query (code) in SQL Server 2008 Management Studio how to add button click event in android studio How to make CREATE OR REPLACE VIEW work in SQL Server? Android findViewById() in Custom View

Examples related to definition

"Multiple definition", "first defined here" errors SOAP vs REST (differences) Error with multiple definitions of function ReferenceError: variable is not defined C# Foreach statement does not contain public definition for GetEnumerator Add Auto-Increment ID to existing table? How to see the CREATE VIEW code for a view in PostgreSQL? Java: int[] array vs int array[] What is a web service endpoint? What is a "thread" (really)?