pg_get_viewdef()

A function for generating a view definition

pg_get_viewdef() is a system function  which generates the SQL required to recreate the specified view or materialized view.

pg_get_viewdef() was added in PostgreSQL 6.4.

Usage

pg_get_viewdef ( view oid [, pretty boolean ] ) → text
pg_get_viewdef ( view oid, wrap_column integer ) → text
pg_get_viewdef ( view text [, pretty boolean ] ) → text

Note that the returned text is reconstructed from metadata and is not the verbatim text of the original CREATE VIEW or CREATE MATERIALIZED VIEW command.

The third variant is deprecated.

Change history

Examples

Basic usage example for pg_get_viewdef() with the following view definition:

postgres=# CREATE TABLE foo (
             id INT NOT NULL,
             val TEXT,
             ts TIMESTAMP WITH TIME ZONE
           );
CREATE TABLE

postgres=# CREATE VIEW bar AS
             SELECT *
               FROM foo
              WHERE ts >= '2022-01-01';
CREATE VIEW

The basic form returns a verbose rendering of the view definition, as would be output by pg_dump:

postgres=# SELECT pg_get_viewdef('bar'::regclass);
                             pg_get_viewdef
-------------------------------------------------------------------------
  SELECT foo.id,                                                        +
     foo.val,                                                           +
     foo.ts                                                             +
    FROM foo                                                            +
   WHERE (foo.ts >= '2022-01-01 00:00:00+09'::timestamp with time zone);
(1 row)

The optional pretty flag results in a less verbose version, primarily avoiding nested parentheses:

postgres=# SELECT pg_get_viewdef('bar', true);
                            pg_get_viewdef
-----------------------------------------------------------------------
  SELECT foo.id,                                                      +
     foo.val,                                                         +
     foo.ts                                                           +
    FROM foo                                                          +
   WHERE foo.ts >= '2022-01-01 00:00:00+09'::timestamp with time zone;
(1 row)

The second version of pg_get_viewdef() enables provision of an integer value which attempts to consolidate lines up until the provided value:

postgres=# SELECT pg_get_viewdef('bar'::regclass, 30);
                            pg_get_viewdef
-----------------------------------------------------------------------
  SELECT foo.id, foo.val,                                             +
     foo.ts                                                           +
    FROM foo                                                          +
   WHERE foo.ts >= '2022-01-01 00:00:00+09'::timestamp with time zone;
(1 row)

postgres=# SELECT pg_get_viewdef('bar'::regclass, 50);
                            pg_get_viewdef
-----------------------------------------------------------------------
  SELECT foo.id, foo.val, foo.ts                                      +
    FROM foo                                                          +
   WHERE foo.ts >= '2022-01-01 00:00:00+09'::timestamp with time zone;
(1 row)

Categories

System catalogue, System function

See also

pg_get_constraintdef(), pg_get_functiondef(), pg_get_indexdef(), pg_get_ruledef(), pg_get_statisticsobjdef(), pg_get_triggerdef()