How to produce DDLs for Views and MViews using pg_dump in the absence of a GUI client.

 

pg_dump is a utility for backing up a PostgreSQL database. It creates consistent backups even when the database is being used concurrently. pg_dump does not prevent other users from using the database.

There are various switches/options supported in the pg_dump and we will be seeing usage of -t option. This option allows to pull pattern match DDL alone with data(only for tables)

-t - option can be used to dump the definition of matching views, materialized views, foreign tables, and sequences

Example: 

 

I created view as given below

postgres=# \d+ test_vw

View "public.test_vw"

Column | Type | Collation | Nullable | Default | Storage | Description

--------+-----------------------+-----------+----------+---------+----------+-------------

i | integer | | | | plain |

seq | double precision | | | | plain |

name | character varying(10) | | | | extended |

View definition:

SELECT i,

i_gen::double precision / 10::double precision AS seq,

name

FROM test;

Let's generate DDL of view using pg_dump.

pg_dump -t public.test_vw

 

pg_dump output:

pg_dump: creating VIEW "public.test_vw"

--

-- TOC entry 216 (class 1259 OID 34287)

-- Name: test_vw; Type: VIEW; Schema: public; Owner: postgres

--

CREATE VIEW public.test_vw AS

SELECT i,

((i_gen)::double precision / (10)::double precision) AS seq,

name

FROM public.test;

ALTER VIEW public.test_vw OWNER TO postgres;

-- Completed on 2024-03-02 04:04:31 UTC

--

-- PostgreSQL database dump complete

--