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.

EoghanM · Accepted Answer · 2022-02-21T11:13:03.390

319

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

edited Feb 21 '22 at 11:13

answered Mar 14 '13 at 22:02

EoghanM

25,161
23
90
123

1

I usually combine this trick with \o command. I dump \d+ to some files then using vim macro i modified those files to supplied my need. – Brain90 May 19 '15 at 07:20
Sad thing is: This cannot be used without psql. The "pure" SQL-command-version (without resorting pg_get_viewdef at all) can, which is more portable, e.g. to Perl with DBI. – Jinxed Jul 23 '15 at 16:34
1

What would be more useful is to be able to edit the view definition code directly with some variation of the \e command, like \ef for functions. A \ev feature would be nice. So far the solution suggested by @Brain90 is the closest I've found to quick editing view definitions. – Thalis K. Jun 02 '16 at 08:53
5

Related tip: `\dv` lists all views – Nathan Long Sep 17 '18 at 18:44
@Jim U edited the answer to `\s+` instead of `\d+` however that isn't valid in my version of postgresql ... he also said "l to list views, s to show code" ... however `\l` lists databases for me ... does anyone know whether any of the above is valid in newer postgresql? – EoghanM Feb 21 '22 at 11:16

score 212 · Answer 2 · answered Jan 31 '13 at 20:36

212

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

answered Jan 31 '13 at 20:36

2

cool, it even pretty-prints it! :) the manual says it is deprecated, though... :( thanks! – Elias Dorneles Jan 31 '13 at 20:41
14

@elias: just use the version that uses an OID by casting the name to an oid: `select pg_get_viewdef('viewname'::regclass, true)` – Jan 31 '13 at 20:44
4

@elias as an alternative to casting, this works too: `SELECT pg_get_viewdef(to_regclass('viewname'))` (requires at least v9.4). – watery Jun 13 '18 at 15:36
1

A big thank you! I have spent at least last 30 minutes trying to find some quick answer, expecting that would be something like "sp_help viewname" like in T-SQL, but not that straightforward. – Stevey Jan 30 '22 at 20:54

score 81 · Answer 3 · answered Jan 31 '13 at 20:29

81

select definition from pg_views where viewname = 'my_view'

answered Jan 31 '13 at 20:29

Clodoaldo Neto

118,695
26
233
260

1

thanks for this one.. it allows to access the view definition from my program rather than just from the psql-client. – Dominik Dorn Jul 19 '14 at 18:01
2

This has the added benefit in that it works for Amazon Redshift as well. – Brent Writes Code Jun 22 '15 at 23:50
This does not work for views in schemas that are not on the search path. And it does not distinguish between two views with the same name in different schemas. When I write schema, I am referring to the namespace that you create with CREATE SCHEMA – Michael Dillon Sep 29 '16 at 02:45
3

@MichaelDillon make a select * instead of select definition, and you will be able to see what schema the view is from, including some other informations. – Anders Kreinøe Jan 23 '17 at 12:49
3

If your view is not on the search path, use `select definition from pg_views where schemaname = 'my_schema' and viewname = 'my_view'` – bonh Apr 22 '19 at 03:28
This is much better than using the function `pg_get_viewdef()`. – Faustin Gashakamba Sep 22 '22 at 12:00

Steve Judd · Answer 4 · 2016-08-09T15:56:37.503

24

If you want an ANSI SQL-92 version:

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

edited Aug 09 '16 at 15:56

answered Aug 09 '16 at 15:48

Steve Judd

241
2
3

Brain90 · Answer 5 · 2021-05-19T02:14:48.833

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

Haha, Just to salute him. he commit a lot to psql in early days. https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=author&s=julian — Brain90, Aug 30 '17 at 07:08

score 12 · Answer 6 · answered Oct 31 '21 at 13:12

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

score 9 · Answer 7 · edited Oct 31 '21 at 13:25

9

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.

edited Oct 31 '21 at 13:25

answered Sep 27 '18 at 11:13

Gianluca Rossini

131
1
3

1

Important detail. This is annoying because I wanted the original "AS SELECT * FROM ..." statement. – villasv Feb 11 '22 at 16:09

score 8 · Answer 8 · answered Sep 21 '21 at 12:47

8

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

\sv <VIEWNAME>

answered Sep 21 '21 at 12:47

trunikov

97
1
4

score -5 · Answer 9 · answered Dec 17 '21 at 05:10

-5

The straightforward way to find the 'CREATE TABLE ...' query is to use this query -

SHOW TABLE your_schema_name.your_table_name

answered Dec 17 '21 at 05:10

Prashant K Tiwari

1
3

2

Hello, welcome to Stackoverflow! This question is for showing the code for a `view`, not for a table. ;-) – Elias Dorneles Dec 17 '21 at 08:12
1

Hey @EliasDorneles, this query works perfectly fine to generate the code (DDL) of a `view` as well. Already tested it. – Prashant K Tiwari Dec 22 '21 at 03:47
@PrashantKTiwari, view joins many tables how will you specify taable_name in your query. Also, many views can be created using the same table then how will you extract the view definition? Here the question is how to generate create view ddl for already existing view by using view_name in query? – santosh tiwary May 17 '23 at 04:46

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

9 Answers9

Linked

Related