README.md

The intention of this SQL file is to generate a text output of the full DDL used to generate a postgres table, based on a database schema and table name.

The script is based on prior work posted on StackOverflow, but extends it to add additional elements and formatting.

The output of the function for a table in the Neotoma Paleoecology Database using the statement:

SELECT * FROM get_table_definition('ap', 'faunranges');

Appears like this:

-- ap.faunranges Table definition

-- Drop table

-- DROP TABLE IF EXISTS ap.faunranges

CREATE TABLE IF NOT EXISTS ap.faunranges (

    gid integer DEFAULT nextval('ap.faunranges_gid_seq'::regclass) NOT NULL,
    spid character varying(10) NULL,
    sciname character varying(254) NULL,
    the_geom geometry(MultiPolygon,4326) NULL

);


-- Table Constraints, Comments and Triggers

--- Table comments
COMMENT ON TABLE ap.faunranges IS 'Faunranges is derived from a set of North American Mammal Species distributions. These layers are used in Neotoma Explorer to provide spatial maps that can be used to compare against fossil distributions.';
COMMENT ON COLUMN ap.faunranges.gid IS 'The unique numeric identifier for the faunal range to be used as a primary key.';
COMMENT ON COLUMN ap.faunranges.spid IS 'A unique four letter species identifier.';
COMMENT ON COLUMN ap.faunranges.sciname IS 'The full specific epithet for the mammal taxon.';
COMMENT ON COLUMN ap.faunranges.the_geom IS 'The geometry for the species range polygon, using ESPG:4326.';

--- Table indices
CREATE UNIQUE INDEX faunranges_pkey ON ap.faunranges USING btree (gid);
CREATE INDEX geom_idx ON ap.faunranges USING gist (the_geom);
CREATE INDEX sciname_idx ON ap.faunranges USING btree (sciname)

--- Remove existing constraints if needed
-- ALTER TABLE ap.faunranges DROP CONSTRAINT IF EXISTS faunranges_pkey;

--- Non-foreign key constraints
ALTER TABLE ap.faunranges ADD CONSTRAINT faunranges_pkey PRIMARY KEY (gid);

--- Foreign Key Restraints

--- Triggers

get_table_definition.sql

-- DROP FUNCTION public.get_table_definition(varchar, varchar);

CREATE OR REPLACE FUNCTION public.get_table_definition(p_schema_name character varying, p_table_name character varying)
 RETURNS SETOF text
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN query 
    WITH table_rec AS (
        SELECT
            c.relname,
            n.nspname,
            c.oid,
            COALESCE(obj_description((p_schema_name||'.'||quote_ident(p_table_name))::regclass), '') AS commentlines
        FROM
            pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE
            relkind = 'r'
            AND n.nspname = p_schema_name
            AND c.relname LIKE p_table_name
        ORDER BY
            c.relname
    ),
    trig_rec AS (
        SELECT tc.*,
               it.trigger_name,
               it.event_manipulation,
               it.event_object_table,
               it.action_statement,
               it.action_orientation,
               it.action_timing
        FROM  information_schema.triggers AS it
        INNER JOIN table_rec AS tc ON tc.nspname = it.event_object_schema AND it.event_object_table = tc.relname
        WHERE event_object_schema = p_schema_name
          AND event_object_table = p_table_name
        ORDER BY event_object_table, event_manipulation
    ),
    col_rec AS (
        SELECT
            a.attname AS colname,
            a.attrelid AS attrelid,
            pg_catalog.format_type(a.atttypid, a.atttypmod) AS coltype,
            a.attrelid AS oid,
            ' DEFAULT ' || (
                SELECT
                    pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM
                    pg_catalog.pg_attrdef d
                WHERE
                    d.adrelid = a.attrelid
                    AND d.adnum = a.attnum
                    AND a.atthasdef) AS column_default_value,
            CASE WHEN a.attnotnull = TRUE THEN
                'NOT NULL'
            ELSE
                'NULL'
            END AS column_not_null,
            a.attnum AS attnum
        FROM
            pg_catalog.pg_attribute a
        WHERE
            a.attnum > 0
            AND NOT a.attisdropped
        ORDER BY
            a.attnum
    ),
    con_rec AS (
        SELECT
            conrelid::regclass::text AS relname,
            n.nspname,
            conname,
            pg_get_constraintdef(c.oid) AS condef,
            contype,
            conrelid AS oid
        FROM
            pg_constraint c
            JOIN pg_namespace n ON n.oid = c.connamespace
    ),
    glue AS (
        SELECT
            format( E'-- %1$I.%2$I Table definition\n\n-- Drop table\n\n-- DROP TABLE IF EXISTS %1$I.%2$I\n\nCREATE TABLE IF NOT EXISTS %1$I.%2$I (\n', p_schema_name, table_rec.relname) AS top,
            format( E'\n);\n\n\n-- Table Constraints, Comments and Triggers') AS bottom,
            oid
        FROM
            table_rec
    ),
    cols AS (
        SELECT
            string_agg(format('    %I %s%s %s', colname, coltype, column_default_value, column_not_null), E',\n') AS lines,
            oid
        FROM
            col_rec
        GROUP BY
            oid
    ),
    constrnt AS (
        SELECT
            string_agg(format('-- ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s;', relname, con_rec.conname), E'\n') AS droplines,
            string_agg(format('ALTER TABLE %s ADD CONSTRAINT %s %s;', relname, con_rec.conname, con_rec.condef), E'\n') AS addlines,
            oid
        FROM
            con_rec
        WHERE
            contype <> 'f'
        GROUP BY
            oid
    ),
    frnkey AS (
        SELECT
            string_agg(format('ALTER TABLE %s ADD CONSTRAINT %s %s;', relname, conname, condef), E'\n') AS lines,
            oid
        FROM
            con_rec
        WHERE
            contype = 'f'
        GROUP BY
            oid
    ),
    indexlines AS (
        SELECT string_agg(format('%s', pgi.indexdef), E';\n') AS indices,
            oid
        FROM table_rec
        LEFT JOIN (SELECT * FROM pg_indexes) AS pgi ON (table_rec.relname = pgi.tablename AND table_rec.nspname = pgi.schemaname)
        GROUP BY oid
    ),
    commentlines AS (
        SELECT
            string_agg(format('COMMENT ON TABLE %1$I.%2$I IS ''%3$s'';', p_schema_name, table_rec.relname, table_rec.commentlines), E'\n') AS lines,
            oid
        FROM
            table_rec
        GROUP BY
            oid
            
    ),
    commentcols AS (
        SELECT
            string_agg(format('COMMENT ON COLUMN %1$I.%2$I.%3$I IS ''%4$s'';', table_rec.nspname, table_rec.relname, col_rec.colname, pgd.description), E'\n') AS lines,
            table_rec.oid
        FROM
            table_rec
			LEFT JOIN col_rec ON col_rec.oid = table_rec.oid
            LEFT OUTER JOIN pg_attribute as pga ON (col_rec.colname = pga.attname AND table_rec.oid = pga.attrelid)
            LEFT OUTER JOIN pg_description AS pgd ON (pgd.objoid = col_rec.oid AND pgd.objsubid = pga.attnum)
        GROUP BY table_rec.oid
    ),
    triggerlines AS (
        SELECT
            string_agg(format('-- DROP TRIGGER IF EXISTS %1$s ON %4$s.%5$s;\n', trigger_name, action_timing, event_manipulation, nspname, relname, action_statement), E'\n') AS trigdrop,
            string_agg(format('CREATE TRIGGER %1$s %2$s %3$s ON %4$s.%5$s FOR EACH ROW %6$s;\n', trigger_name, action_timing, event_manipulation, nspname, relname, action_statement), E'\n') AS trigadd,
            oid
        FROM
            trig_rec
        GROUP BY oid
    )
    SELECT
        concat_ws(E'\n',
            glue.top,
            cols.lines,
            glue.bottom,
            E'\n--- Table comments', commentlines.lines, commentcols.lines,
            E'\n--- Table indices', indexlines.indices,
            E'\n--- Remove existing constraints if needed', constrnt.droplines,
            E'\n--- Non-foreign key constraints', constrnt.addlines,
            E'\n--- Foreign Key Restraints', frnkey.lines,
            E'\n--- Triggers', triggerlines.trigdrop, triggerlines.trigadd)
    FROM
        glue
        JOIN cols ON cols.oid = glue.oid
        LEFT JOIN constrnt ON constrnt.oid = glue.oid
        LEFT JOIN commentlines ON commentlines.oid = glue.oid
		LEFT JOIN commentcols ON commentcols.oid = glue.oid
        LEFT JOIN indexlines ON indexlines.oid = glue.oid
        LEFT JOIN frnkey ON frnkey.oid = glue.oid
        LEFT JOIN triggerlines ON triggerlines.oid = glue.oid;
END;
$function$
;