Extracting DDL of Objects

There are many methods to get the DDL of the objects.

Using PostgreSQL executable pg_dump

The utility pg_dump is used to dump the DDL and/or data of the schema and tables.

You can run pg_dump with -s (–schema-only) and -t (–table) option to generate DDL of required tables. The option -s dictates that only DDL has to be generated and the option -t dictates that we want DDL of only tables to be generated.

Let’s look at the examples.

The below example will generate the DDL of the table city in database appdb.

$ pg_dump -d appdb -s -t city

postgres@jupiter:~$ pg_dump -d appdb -s -t city
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.3 (Ubuntu 13.3-1.pgdg20.04+1)
-- Dumped by pg_dump version 13.3 (Ubuntu 13.3-1.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: city; Type: TABLE; Schema: public; Owner: manoj
--

CREATE TABLE public.city (
    city_id integer NOT NULL,
    name text,
    pop integer
);


ALTER TABLE public.city OWNER TO manoj;

--
-- Name: city_city_id_seq; Type: SEQUENCE; Schema: public; Owner: manoj
--

CREATE SEQUENCE public.city_city_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.city_city_id_seq OWNER TO manoj;

--
-- Name: city_city_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: manoj
--

ALTER SEQUENCE public.city_city_id_seq OWNED BY public.city.city_id;


--
-- Name: city city_id; Type: DEFAULT; Schema: public; Owner: manoj
--

ALTER TABLE ONLY public.city ALTER COLUMN city_id SET DEFAULT nextval('public.city_city_id_seq'::regclass);


--
-- Name: city city_pkey; Type: CONSTRAINT; Schema: public; Owner: manoj
--

ALTER TABLE ONLY public.city
    ADD CONSTRAINT city_pkey PRIMARY KEY (city_id);


--
-- Name: idx_city_high_name; Type: INDEX; Schema: public; Owner: manoj
--

CREATE INDEX idx_city_high_name ON public.city USING btree (name);


--
-- PostgreSQL database dump complete
--

postgres@jupiter:~$

The below example will generate the DDL of the tables city and street in the database appdb.

$ pg_dump -d appdb -s -t city -t street

The below example will generate the DDL of all objects in the database appdb, including tables, views, functions, etc.

$ pg_dump -d appdb -s

The below example will generate the DDL of all objects in the database appdb, excluding the table city and write it into a file.

$ pg_dump -d appdb -s -T city -f ddl.sql

Using psql command \d

You can get the DDL just by running \d. This works only for table-like objects.

Let’s look at the examples.

The below example will generate the DDL of the table city. This, however, doesn’t generate the exact command to create the table.

# \d+ city

appdb=# \d+ city
                                                   Table "public.city"
 Column  |  Type   | Collation | Nullable |                Default                | Storage  | Stats target | Description 
---------+---------+-----------+----------+---------------------------------------+----------+--------------+-------------
 city_id | integer |           | not null | nextval('city_city_id_seq'::regclass) | plain    |              | 
 name    | text    |           |          |                                       | extended |              | 
 pop     | integer |           |          |                                       | plain    |              | 
Indexes:
    "city_pkey" PRIMARY KEY, btree (city_id)
    "idx_city_high_name" btree (name)
Referenced by:
    TABLE "street" CONSTRAINT "street_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id)
Access method: heap

appdb=#

The below example will generate the DDL of the view city_high.

# \d+ city_high

appdb=# \d+ city_high
                           View "public.city_high"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description 
---------+---------+-----------+----------+---------+----------+-------------
 city_id | integer |           |          |         | plain    | 
 name    | text    |           |          |         | extended | 
 pop     | integer |           |          |         | plain    | 
View definition:
 SELECT city.city_id,
    city.name,
    city.pop
   FROM city
  WHERE city.pop > 10000;

appdb=#

The below example will generate the DDL of the function dup.

# \x on
# \df+ dup

appdb=# \x on
Expanded display is on.
appdb=# \df+ dup
List of functions
-[ RECORD 1 ]-------+-------------------------------------------
Schema              | public
Name                | dup
Result data type    | record
Argument data types | integer, OUT f1 integer, OUT f2 text
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | manoj
Security            | invoker
Access privileges   | 
Language            | sql
Source code         |  SELECT $1, CAST($1 AS text) || ' is text'+
                    | UNION ALL                                 +
                    | SELECT $1, CAST($1 AS text) || ' is text2'+
                    | 
Description         | 

appdb=#

Using inbuilt functions (pg_get_constraintdef, pg_get_indexdef, etc)

There are many inbuilt functions to extract the DDL for various types of the objects. But, there is no method to get the DDL of a table.

Let’s look at the examples.

The below example will generate the DDL of the view city_high in database appdb.

# select pg_get_viewdef('city_high'::regclass, true);

appdb=# select pg_get_viewdef('city_high'::regclass, true);
      pg_get_viewdef       
---------------------------
  SELECT city.city_id,    +
     city.name,           +
     city.pop             +
    FROM city             +
   WHERE city.pop > 10000;
(1 row)

appdb=#

The below example will generate the DDL of the function multipg in database appdb.

# SELECT proname, pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public'
AND f.proname = 'multipg';

appdb=# SELECT proname, pg_get_functiondef(f.oid)
appdb-# FROM pg_catalog.pg_proc f
appdb-# INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
appdb-# WHERE n.nspname = 'public'
appdb-# AND f.proname = 'multipg';
 proname |                       pg_get_functiondef                        
---------+-----------------------------------------------------------------
 multipg | CREATE OR REPLACE FUNCTION public.multipg(a integer, b integer)+
         |  RETURNS integer                                               +
         |  LANGUAGE plpgsql                                              +
         | AS $function$                                                  +
         |     DECLARE                                                    +
         |     c integer;                                                 +
         |     BEGIN                                                      +
         |      c = a * b;                                                +
         |      RETURN c;                                                 +
         |     END;                                                       +
         |     $function$                                                 +
         | 
(1 row)

appdb=#

Alternatively, you can do the below as well.

# SELECT proname, prosrc FROM pg_proc WHERE proname = 'multipg';

appdb=# select proname, prosrc from pg_proc where proname = 'multipg';
 proname |     prosrc      
---------+-----------------
 multipg |                +
         |     DECLARE    +
         |     c integer; +
         |     BEGIN      +
         |      c = a * b;+
         |      RETURN c; +
         |     END;       +
         |     
(1 row)

appdb=#

# SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'multipg';

appdb=# SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'multipg';
                       pg_get_functiondef                        
-----------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.multipg(a integer, b integer)+
  RETURNS integer                                               +
  LANGUAGE plpgsql                                              +
 AS $function$                                                  +
     DECLARE                                                    +
     c integer;                                                 +
     BEGIN                                                      +
      c = a * b;                                                +
      RETURN c;                                                 +
     END;                                                       +
     $function$                                                 +
 
(1 row)

appdb=#

Similarly, we can get the DDL of an index.

# SELECT pg_get_indexdef('city_pkey'::regclass);

appdb=# SELECT pg_get_indexdef('city_pkey'::regclass);
                          pg_get_indexdef                           
--------------------------------------------------------------------
 CREATE UNIQUE INDEX city_pkey ON public.city USING btree (city_id)
(1 row)

appdb=#

The below method gives the DDL of all indexes of a table.

# SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index WHERE  indrelid = 'city'::regclass;

appdb=# SELECT pg_get_indexdef(indexrelid) AS index_query
appdb-# FROM pg_index WHERE  indrelid = 'city'::regclass;
                            index_query                             
--------------------------------------------------------------------
 CREATE UNIQUE INDEX city_pkey ON public.city USING btree (city_id)
 CREATE INDEX idx_city_high_name ON public.city USING btree (name)
(2 rows)

appdb=#

Using SQLs on metadata views

The various metadata views can be joined to get the DDLs of all kind of objects.

Let’s look at the examples.

The below example will generate the DDL of the table city in a tabular form.

# SELECT table_name, column_name, data_type, character_maximum_length, column_default, is_nullable
FROM information_schema.columns 
WHERE table_name = 'city';

appdb=# SELECT table_name, column_name, data_type, character_maximum_length, column_default, is_nullable
appdb-# FROM information_schema.columns 
appdb-# WHERE table_name = 'city';
 table_name | column_name | data_type | character_maximum_length |            column_default             | is_nullable 
------------+-------------+-----------+--------------------------+---------------------------------------+-------------
 city       | city_id     | integer   |                          | nextval('city_city_id_seq'::regclass) | NO
 city       | name        | text      |                          |                                       | YES
 city       | pop         | integer   |                          |                                       | YES
(3 rows)

appdb=#

The below example will generate the DDL of the table city in a better format.

# \pset linestyle old-ascii

# SELECT                                          
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT 
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case 
      when a.attnotnull
    then 'NOT NULL' 
    else 'NULL' 
    END as not_null 
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'city'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname
;

appdb=# SELECT                                          
appdb-#   'CREATE TABLE ' || relname || E'\n(\n' ||
appdb-#   array_to_string(
appdb(#     array_agg(
appdb(#       '    ' || column_name || ' ' ||  type || ' '|| not_null
appdb(#     )
appdb(#     , E',\n'
appdb(#   ) || E'\n);\n'
appdb-# from
appdb-# (
appdb(#   SELECT 
appdb(#     c.relname, a.attname AS column_name,
appdb(#     pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
appdb(#     case 
appdb(#       when a.attnotnull
appdb(#     then 'NOT NULL' 
appdb(#     else 'NULL' 
appdb(#     END as not_null 
appdb(#   FROM pg_class c,
appdb(#    pg_attribute a,
appdb(#    pg_type t
appdb(#    WHERE c.relname = 'city'
appdb(#    AND a.attnum > 0
appdb(#    AND a.attrelid = c.oid
appdb(#    AND a.atttypid = t.oid
appdb(#  ORDER BY a.attnum
appdb(# ) as tabledefinition
appdb-# group by relname
appdb-# ;
           ?column?            
-------------------------------
 CREATE TABLE city             
 (                             
     city_id integer NOT NULL, 
     name text NULL,           
     pop integer NULL          
 );                            
 
(1 row)

appdb=#

The below example will generate the DDL of the function dup in database appdb.

# SELECT p.proname AS procedure_name,
          p.pronargs AS num_args,
          t1.typname AS return_type,
          a.rolname AS procedure_owner,
          l.lanname AS language_type,
          p.proargtypes AS argument_types_oids,
          prosrc AS body
     FROM pg_proc p
LEFT JOIN pg_type t1 ON p.prorettype=t1.oid   
LEFT JOIN pg_authid a ON p.proowner=a.oid 
LEFT JOIN pg_language l ON p.prolang=l.oid
    WHERE proname = 'dup';

appdb=# SELECT p.proname AS procedure_name,
appdb-#           p.pronargs AS num_args,
appdb-#           t1.typname AS return_type,
appdb-#           a.rolname AS procedure_owner,
appdb-#           l.lanname AS language_type,
appdb-#           p.proargtypes AS argument_types_oids,
appdb-#           prosrc AS body
appdb-#      FROM pg_proc p
appdb-# LEFT JOIN pg_type t1 ON p.prorettype=t1.oid   
appdb-# LEFT JOIN pg_authid a ON p.proowner=a.oid 
appdb-# LEFT JOIN pg_language l ON p.prolang=l.oid
appdb-#     WHERE proname = 'dup';
 procedure_name | num_args | return_type | procedure_owner | language_type | argument_types_oids |                    body                    
----------------+----------+-------------+-----------------+---------------+---------------------+--------------------------------------------
 dup            |        1 | record      | manoj           | sql           | 23                  |  SELECT $1, CAST($1 AS text) || ' is text'+
                |          |             |                 |               |                     | UNION ALL                                 +
                |          |             |                 |               |                     | SELECT $1, CAST($1 AS text) || ' is text2'+
                |          |             |                 |               |                     | 
(1 row)

appdb=#

All about PostgreSQL

Contents

Contents

Getting Started with PostgreSQL

Advanced PostgreSQL

Miscellaneous

admin

Share post:

Leave a Comment Cancel reply