CODEWITHSUNDEEP
databasepostgresqlviewdefinitionsql-view
Elias Dorneles
Elias Dorneles

Reputation: 23796

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

Upvotes: 278

Views: 231062

Answers (9)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you create person table and my_v view as shown below:

CREATE TABLE person (
  id INTEGER,
  name VARCHAR(20)
);

CREATE VIEW my_v AS
  SELECT * FROM person;

Then, you can show the code of my_v view with \sv as shown below:

postgres=# \sv public.my_v
CREATE OR REPLACE VIEW public.my_v AS
 SELECT id,
    name
   FROM person

postgres=# \sv+ public.my_v
1          CREATE OR REPLACE VIEW public.my_v AS
2           SELECT id,
3              name
4             FROM person

*Memos:

  • + can show line numbers.

  • You can omit the schema public..

Or, you can show the code of my_v view with pg_get_viewdef() as shown below:

postgres=# SELECT pg_get_viewdef('public.my_v');
 pg_get_viewdef
-----------------
  SELECT id,    +
     name       +
    FROM person;
(1 row)

postgres=# SELECT pg_get_viewdef('public.my_v'::regclass);
 pg_get_viewdef
-----------------
  SELECT id,    +
     name       +
    FROM person;
(1 row)

*Memos:

  • You can use a view name or OID(Object identifier)forpg_get_viewdef()`.

  • You can omit the schema public..

Or, you can show the code of my_v view with \d+ <view-name> as shown below:

postgres=# \d+ public.my_v
                                    View "public.my_v"
 Column |         Type          | Collation | Nullable | Default | Storage  | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
 id     | integer               |           |          |         | plain    |
 name   | character varying(20) |           |          |         | extended |
View definition:
 SELECT id,
    name
   FROM person;

*Memos:

  • You must set + and <view-name> otherwise the code is not shown.

  • You can omit the schema public..

Or, you can show the code of my_v view with pg_views as shown below:

postgres=# SELECT definition FROM pg_views WHERE viewname = 'my_v';
   definition
-----------------
  SELECT id,    +
     name       +
    FROM person;
(1 row)

Or, you can show the code of my_v view with information_schema.views as shown below:

postgres=# SELECT view_definition FROM information_schema.views WHERE table_name = 'my_v';
 view_definition
-----------------
  SELECT id,    +
     name       +
    FROM person;
(1 row)

Upvotes: 1

trunikov
trunikov

Reputation: 117

In the command line client psql you can use following command:

\dv <VIEWNAME>

Upvotes: 9

EoghanM
EoghanM

Reputation: 26924

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

Upvotes: 340

Gianluca Rossini
Gianluca Rossini

Reputation: 161

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.

Upvotes: 11

Delta
Delta

Reputation: 148

In psql cli , you can use

\d+ <yourViewName>
\sv <yourViewName>

Output as follows:

\d+ v_ma_students

                               View "public.v_ma_students"
 Column |         Type          | Collation | Nullable | Default | Storage  | De
scription
--------+-----------------------+-----------+----------+---------+----------+---
SOMETHINGS HERE

View definition:
 SELECT student.sno,
    student.sname,
    student.ssex,
    student.sage,
    student.sdept
   FROM student
  WHERE student.sdept::text = 'MA'::text;
Options: check_option=cascaded


\sv v_ma_students

CREATE OR REPLACE VIEW public.v_ma_students AS
 SELECT student.sno,
    student.sname,
    student.ssex,
    student.sage,
    student.sdept
   FROM student
  WHERE student.sdept::text = 'MA'::text
 WITH CASCADED CHECK OPTION

Upvotes: 12

Brain90
Brain90

Reputation: 1694

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

Upvotes: 15

Steve Judd
Steve Judd

Reputation: 261

If you want an ANSI SQL-92 version:

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

Upvotes: 26

user330315
user330315

Reputation: 

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

Upvotes: 238

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select definition from pg_views where viewname = 'my_view'

Upvotes: 85

Related Questions