WITH
config AS (
SELECT
'aep1' AS schema_name,
TRUE AS include_checksums,
TRUE AS include_domains,
TRUE AS include_rules,
TRUE AS include_rls,
TRUE AS include_comments,
TRUE AS include_grants,
TRUE AS include_indexes,
TRUE AS include_triggers,
TRUE AS include_sequences,
TRUE AS include_functions,
TRUE AS include_views,
TRUE AS include_matviews,
TRUE AS include_extensions,
TRUE AS include_table_comments,
TRUE AS include_column_comments,
FALSE AS include_drop_statements,
FALSE AS only_dependent_objects,
NULL::text[] AS include_tables,
NULL::text[] AS exclude_objects
),
extensions_ddl AS (
SELECT
NULL AS nspname,
extname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP EXTENSION IF EXISTS ' || extname || ';' || E'\n'
ELSE ''
END ||
'CREATE EXTENSION IF NOT EXISTS ' || extname ||
' WITH SCHEMA ' || extnamespace::regnamespace || ';' AS ddl,
md5('CREATE EXTENSION ' || extname) AS object_checksum,
'EXTENSION' AS object_type,
1 AS sort_order
FROM pg_extension
CROSS JOIN config
WHERE extnamespace::regnamespace::text = config.schema_name
AND config.include_extensions
),
domains_ddl AS (
SELECT
n.nspname,
t.typname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP DOMAIN ' || n.nspname || '.' || t.typname || ';' || E'\n'
ELSE ''
END ||
'CREATE DOMAIN ' || n.nspname || '.' || t.typname || ' AS ' ||
pg_catalog.format_type(t.typbasetype, t.typtypmod) ||
CASE WHEN t.typnotnull THEN ' NOT NULL' ELSE '' END ||
CASE WHEN t.typdefault IS NOT NULL THEN ' DEFAULT ' || t.typdefault ELSE '' END || ';' || E'\n' ||
'ALTER DOMAIN ' || n.nspname || '.' || t.typname ||
' OWNER TO ' || pg_get_userbyid(t.typowner) || ';' AS ddl,
md5('CREATE DOMAIN ' || n.nspname || '.' || t.typname || ' AS ' ||
pg_catalog.format_type(t.typbasetype, t.typtypmod) ||
CASE WHEN t.typnotnull THEN ' NOT NULL' ELSE '' END ||
CASE WHEN t.typdefault IS NOT NULL THEN ' DEFAULT ' || t.typdefault ELSE '' END) AS object_checksum,
'DOMAIN' AS object_type,
2 AS sort_order
FROM pg_type t
JOIN pg_namespace n ON n.oid = t.typnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND t.typtype = 'd'
AND config.include_domains
),
all_tables AS (
SELECT
n.nspname,
c.relname,
c.oid,
ts.spcname,
c.relowner,
c.relkind,
c.relispartition,
(SELECT string_agg(
a.attname || ' ' ||
pg_catalog.format_type(a.atttypid, a.atttypmod) ||
CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END,
', '
) FROM pg_attribute a
WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped) AS columns,
CASE WHEN c.relkind = 'p' THEN
'PARTITION BY ' ||
CASE WHEN pg_get_partkeydef(c.oid) LIKE 'RANGE %' THEN
substr(pg_get_partkeydef(c.oid), 7)
WHEN pg_get_partkeydef(c.oid) LIKE 'LIST %' THEN
substr(pg_get_partkeydef(c.oid), 6)
WHEN pg_get_partkeydef(c.oid) LIKE 'HASH %' THEN
substr(pg_get_partkeydef(c.oid), 6)
ELSE pg_get_partkeydef(c.oid)
END
ELSE '' END AS partition_def,
CASE WHEN c.relispartition THEN
'PARTITION OF ' ||
(SELECT n2.nspname FROM pg_inherits i
JOIN pg_class pc ON i.inhparent = pc.oid
JOIN pg_namespace n2 ON pc.relnamespace = n2.oid
WHERE i.inhrelid = c.oid) ||
'.' || (SELECT pc.relname FROM pg_inherits i
JOIN pg_class pc ON i.inhparent = pc.oid
WHERE i.inhrelid = c.oid) || ' ' ||
pg_get_expr(c.relpartbound, c.oid)
ELSE '' END AS partition_of_def,
(SELECT string_agg(
'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(oid),
', '
) FROM pg_constraint
WHERE conrelid = c.oid AND contype = 'f') AS foreign_keys,
(SELECT string_agg(
'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname ||
' IS ' || quote_literal(d.description) || ';', E'\n'
) FROM pg_attribute a
LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum
WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped AND d.description IS NOT NULL) AS column_comments
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace ts ON ts.oid = c.reltablespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.relkind IN ('r', 'p')
AND (config.include_tables IS NULL OR c.relname = ANY(config.include_tables))
),
collations_ddl AS (
SELECT
n.nspname,
c.collname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP COLLATION ' || n.nspname || '.' || c.collname || ';' || E'\n'
ELSE ''
END ||
'CREATE COLLATION ' || n.nspname || '.' || c.collname || E'\n' ||
' (LC_COLLATE = ' || quote_literal(c.collcollate) || ', ' || E'\n' ||
' LC_CTYPE = ' || quote_literal(c.collctype) || ')' || E'\n' ||
CASE WHEN c.collprovider <> 'd' THEN ' PROVIDER = ' || c.collprovider::text || E'\n' ELSE '' END ||
CASE WHEN c.collisdeterministic THEN ' DETERMINISTIC = TRUE' || E'\n' ELSE '' END ||
';' || E'\n' ||
'ALTER COLLATION ' || n.nspname || '.' || c.collname ||
' OWNER TO ' || pg_get_userbyid(c.collowner) || ';' AS ddl,
md5('CREATE COLLATION ' || n.nspname || '.' || c.collname) AS object_checksum,
'COLLATION' AS object_type,
12 AS sort_order
FROM pg_collation c
JOIN pg_namespace n ON n.oid = c.collnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.collname NOT LIKE 'pg_%'
AND config.include_extensions
),
casts_ddl AS (
SELECT
NULL AS nspname,
format_type(castsource, NULL) || ' AS ' || format_type(casttarget, NULL) AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP CAST (' || format_type(castsource, NULL) || ' AS ' || format_type(casttarget, NULL) || ');' || E'\n'
ELSE ''
END ||
'CREATE CAST (' || format_type(castsource, NULL) || ' AS ' || format_type(casttarget, NULL) || ')' || E'\n' ||
CASE WHEN castfunc <> 0
THEN ' WITH FUNCTION ' || (SELECT p.proname FROM pg_proc p WHERE p.oid = c.castfunc) || ';'
ELSE ' WITHOUT FUNCTION;' END || E'\n' ||
CASE WHEN castcontext = 'a' THEN '-- (ASSIGNMENT cast)' || E'\n'
WHEN castcontext = 'i' THEN '-- (IMPLICIT cast)' || E'\n'
ELSE '' END AS ddl,
md5('CREATE CAST ' || format_type(castsource, NULL) || ' AS ' || format_type(casttarget, NULL)) AS object_checksum,
'CAST' AS object_type,
13 AS sort_order
FROM pg_cast c
CROSS JOIN config
WHERE (castsource IN (SELECT oid FROM pg_type WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = config.schema_name))
OR casttarget IN (SELECT oid FROM pg_type WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = config.schema_name)))
AND config.include_extensions
),
operators_ddl AS (
SELECT
n.nspname,
o.oprname || '(' ||
format_type(o.oprleft, NULL) || ', ' || format_type(o.oprright, NULL) ||
')' AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP OPERATOR ' || n.nspname || '.' || o.oprname ||
'(' || format_type(o.oprleft, NULL) || ', ' || format_type(o.oprright, NULL) || ');' || E'\n'
ELSE ''
END ||
'CREATE OPERATOR ' || n.nspname || '.' || o.oprname || E'\n' ||
' (LEFTARG = ' || format_type(o.oprleft, NULL) || ',' || E'\n' ||
' RIGHTARG = ' || format_type(o.oprright, NULL) || ',' || E'\n' ||
' PROCEDURE = ' || (SELECT p.proname FROM pg_proc p WHERE p.oid = o.oprcode) || ',' || E'\n' ||
CASE WHEN o.oprcom <> 0
THEN ' COMMUTATOR = ' || (SELECT op.oprname FROM pg_operator op WHERE op.oid = o.oprcom)::text || ',' || E'\n'
ELSE '' END ||
CASE WHEN o.oprnegate <> 0
THEN ' NEGATOR = ' || (SELECT op.oprname FROM pg_operator op WHERE op.oid = o.oprnegate)::text || ',' || E'\n'
ELSE '' END ||
' RESTRICT = ' || (SELECT p.proname FROM pg_proc p WHERE p.oid = o.oprrest) || ',' || E'\n' ||
' JOIN = ' || (SELECT p.proname FROM pg_proc p WHERE p.oid = o.oprjoin) || E'\n' ||
' );' AS ddl,
md5('CREATE OPERATOR ' || n.nspname || '.' || o.oprname) AS object_checksum,
'OPERATOR' AS object_type,
14 AS sort_order
FROM pg_operator o
JOIN pg_namespace n ON n.oid = o.oprnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND config.include_functions
),
table_ddls AS (
SELECT
t.nspname,
t.relname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP TABLE ' || t.nspname || '.' || t.relname || ';' || E'\n\n'
ELSE ''
END ||
'CREATE ' ||
CASE WHEN t.relkind = 'p' THEN 'TABLE ' || t.nspname || '.' || t.relname || ' (' ||
t.columns ||
CASE WHEN t.foreign_keys IS NOT NULL THEN ', ' || t.foreign_keys ELSE '' END ||
')' || E'\n' ||
t.partition_def || ';'
WHEN t.relispartition THEN 'TABLE ' || t.nspname || '.' || t.relname || ' ' || t.partition_of_def || ';'
ELSE 'TABLE ' || t.nspname || '.' || t.relname || ' (' ||
t.columns ||
CASE WHEN t.foreign_keys IS NOT NULL THEN ', ' || t.foreign_keys ELSE '' END ||
')' ||
CASE WHEN t.spcname IS NOT NULL THEN E'\nTABLESPACE ' || t.spcname ELSE '' END || ';'
END || E'\n\n' ||
CASE WHEN config.include_column_comments THEN COALESCE(t.column_comments || E'\n\n', '') ELSE '' END ||
CASE WHEN config.include_indexes THEN
(SELECT string_agg(pg_get_indexdef(i.indexrelid) || ';', E'\n')
FROM pg_index i
WHERE i.indrelid = t.oid) || E'\n\n'
ELSE '' END ||
'ALTER TABLE ' || t.nspname || '.' || t.relname ||
' OWNER TO ' || pg_get_userbyid(t.relowner) || ';' || E'\n' ||
CASE WHEN config.include_grants THEN
(SELECT string_agg(
'GRANT ' || privilege_type || ' ON TABLE ' ||
table_schema || '.' || table_name || ' TO ' || grantee || ';', E'\n')
FROM information_schema.table_privileges
WHERE table_schema = t.nspname AND table_name = t.relname) || E'\n\n'
ELSE '' END ||
'-- ************************************************************' || E'\n'
AS ddl,
md5('CREATE TABLE ' || t.nspname || '.' || t.relname || ' (' || t.columns || ')') AS object_checksum,
CASE
WHEN t.relkind = 'p' THEN 'PARTITIONED TABLE'
WHEN t.relispartition THEN 'TABLE PARTITION'
ELSE 'TABLE'
END AS object_type,
3 AS sort_order
FROM all_tables t
CROSS JOIN config
),
sequences_ddl AS (
SELECT
n.nspname,
c.relname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP SEQUENCE ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
'CREATE SEQUENCE ' || n.nspname || '.' || c.relname || E'\n' ||
' START WITH ' || s.seqstart || E'\n' ||
' INCREMENT BY ' || s.seqincrement || E'\n' ||
' MINVALUE ' || s.seqmin || E'\n' ||
' MAXVALUE ' || s.seqmax || E'\n' ||
' CACHE ' || s.seqcache || ';' || E'\n' ||
'ALTER SEQUENCE ' || n.nspname || '.' || c.relname ||
' OWNER TO ' || pg_get_userbyid(c.relowner) || ';' AS ddl,
md5('CREATE SEQUENCE ' || n.nspname || '.' || c.relname || ' START ' ||
s.seqstart || ' INCREMENT ' || s.seqincrement) AS object_checksum,
'SEQUENCE' AS object_type,
4 AS sort_order
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_sequence s ON s.seqrelid = c.oid
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.relkind = 'S'
AND config.include_sequences
),
functions_ddl AS (
SELECT
n.nspname,
p.proname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP FUNCTION IF EXISTS ' || n.nspname || '.' || p.proname || '(' ||
oidvectortypes(p.proargtypes) || ');' || E'\n'
ELSE ''
END ||
pg_get_functiondef(p.oid) || ';' AS ddl,
md5(pg_get_functiondef(p.oid)) AS object_checksum,
'FUNCTION' AS object_type,
5 AS sort_order
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND config.include_functions
),
views_ddl AS (
SELECT
n.nspname,
c.relname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP VIEW ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
pg_get_viewdef(c.oid, true) || ';' || E'\n' ||
'ALTER VIEW ' || n.nspname || '.' || c.relname ||
' OWNER TO ' || pg_get_userbyid(c.relowner) || ';' AS ddl,
md5(pg_get_viewdef(c.oid, true)) AS object_checksum,
'VIEW' AS object_type,
6 AS sort_order
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.relkind = 'v'
AND config.include_views
),
matviews_ddl AS (
SELECT
n.nspname,
c.relname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP MATERIALIZED VIEW ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
'CREATE MATERIALIZED VIEW ' || n.nspname || '.' || c.relname ||
' AS ' || pg_get_viewdef(c.oid, true) || ' WITH NO DATA;' || E'\n' ||
'ALTER MATERIALIZED VIEW ' || n.nspname || '.' || c.relname ||
' OWNER TO ' || pg_get_userbyid(c.relowner) || ';' AS ddl,
md5(pg_get_viewdef(c.oid, true)) AS object_checksum,
'MATERIALIZED VIEW' AS object_type,
7 AS sort_order
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.relkind = 'm'
AND config.include_matviews
),
rules_ddl AS (
SELECT
n.nspname,
r.rulename AS object_name,
c.relname AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP RULE ' || r.rulename || ' ON ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
pg_get_ruledef(r.oid) || ';' AS ddl,
md5(pg_get_ruledef(r.oid)) AS object_checksum,
'RULE' AS object_type,
8 AS sort_order
FROM pg_rewrite r
JOIN pg_class c ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND r.rulename != '_RETURN'
AND config.include_rules
),
triggers_ddl AS (
SELECT
n.nspname,
t.tgname AS object_name,
c.relname AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP TRIGGER ' || t.tgname || ' ON ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
'CREATE TRIGGER ' || t.tgname || E'\n' ||
' ' || CASE WHEN t.tgtype & 1 > 0 THEN 'BEFORE' ELSE 'AFTER' END || ' ' ||
TRIM(TRAILING ' OR ' FROM
CASE WHEN t.tgtype & 2 > 0 THEN 'INSERT OR ' ELSE '' END ||
CASE WHEN t.tgtype & 4 > 0 THEN 'DELETE OR ' ELSE '' END ||
CASE WHEN t.tgtype & 8 > 0 THEN 'UPDATE OR ' ELSE '' END ||
CASE WHEN t.tgtype & 16 > 0 THEN 'TRUNCATE OR ' ELSE '' END
) || ' ON ' || n.nspname || '.' || c.relname || E'\n' ||
' FOR EACH ' || CASE WHEN t.tgtype & 32 > 0 THEN 'ROW' ELSE 'STATEMENT' END || E'\n' ||
' EXECUTE FUNCTION ' || p.proname || '(' ||
(SELECT string_agg(param_type::regtype::text, ', ')
FROM unnest(p.proargtypes) AS param_type) || ');' AS ddl,
md5('CREATE TRIGGER ' || t.tgname || ' ON ' || n.nspname || '.' || c.relname) AS object_checksum,
'TRIGGER' AS object_type,
9 AS sort_order
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_proc p ON p.oid = t.tgfoid
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND NOT t.tgisinternal
AND config.include_triggers
),
fdw_ddl AS (
SELECT
NULL AS nspname,
f.fdwname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP FOREIGN DATA WRAPPER ' || f.fdwname || ';' || E'\n'
ELSE ''
END ||
'CREATE FOREIGN DATA WRAPPER ' || f.fdwname || E'\n' ||
CASE WHEN f.fdwhandler <> 0
THEN ' HANDLER ' || (SELECT proname FROM pg_proc WHERE oid = f.fdwhandler) || E'\n'
ELSE '' END ||
CASE WHEN f.fdwvalidator <> 0
THEN ' VALIDATOR ' || (SELECT proname FROM pg_proc WHERE oid = f.fdwvalidator) || E'\n'
ELSE '' END ||
' OPTIONS (' || (
SELECT string_agg(option_name || ' ' || quote_literal(option_value), ', ')
FROM pg_options_to_table(f.fdwoptions)
) || ');' || E'\n' ||
'ALTER FOREIGN DATA WRAPPER ' || f.fdwname || ' OWNER TO ' || pg_get_userbyid(f.fdwowner) || ';' AS ddl,
md5('CREATE FOREIGN DATA WRAPPER ' || f.fdwname) AS object_checksum,
'FOREIGN DATA WRAPPER' AS object_type,
20 AS sort_order
FROM pg_foreign_data_wrapper f
CROSS JOIN config
WHERE config.include_extensions
),
foreign_servers_ddl AS (
SELECT
NULL AS nspname,
s.srvname AS object_name,
NULL AS table_name,
f.fdwname AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP SERVER ' || s.srvname || ';' || E'\n'
ELSE ''
END ||
'CREATE SERVER ' || s.srvname || E'\n' ||
' FOREIGN DATA WRAPPER ' || f.fdwname || E'\n' ||
CASE WHEN s.srvtype IS NOT NULL
THEN ' TYPE ' || quote_literal(s.srvtype) || E'\n'
ELSE '' END ||
CASE WHEN s.srvversion IS NOT NULL
THEN ' VERSION ' || quote_literal(s.srvversion) || E'\n'
ELSE '' END ||
' OPTIONS (' || (
SELECT string_agg(option_name || ' ' || quote_literal(option_value), ', ')
FROM pg_options_to_table(s.srvoptions)
) || ');' || E'\n' ||
'ALTER SERVER ' || s.srvname || ' OWNER TO ' || pg_get_userbyid(s.srvowner) || ';' AS ddl,
md5('CREATE SERVER ' || s.srvname) AS object_checksum,
'FOREIGN SERVER' AS object_type,
21 AS sort_order
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON f.oid = s.srvfdw
CROSS JOIN config
WHERE config.include_extensions
),
rls_ddl AS (
SELECT
n.nspname,
pol.polname AS object_name,
c.relname AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP POLICY ' || pol.polname || ' ON ' || n.nspname || '.' || c.relname || ';' || E'\n'
ELSE ''
END ||
'CREATE POLICY ' || pol.polname || ' ON ' || n.nspname || '.' || c.relname || E'\n' ||
' ' || CASE WHEN pol.polpermissive THEN 'PERMISSIVE' ELSE 'RESTRICTIVE' END || E'\n' ||
' FOR ' ||
CASE
WHEN pol.polcmd = 'r' THEN 'SELECT'
WHEN pol.polcmd = 'a' THEN 'INSERT'
WHEN pol.polcmd = 'w' THEN 'UPDATE'
WHEN pol.polcmd = 'd' THEN 'DELETE'
WHEN pol.polcmd = '*' THEN 'ALL'
END || E'\n' ||
' TO ' ||
(SELECT string_agg(
CASE WHEN pg_get_userbyid(role) = 'public' THEN 'PUBLIC' ELSE pg_get_userbyid(role) END, ', '
) FROM unnest(pol.polroles) AS role) || E'\n' ||
CASE WHEN pol.polqual IS NOT NULL THEN
' USING (' || pg_get_expr(pol.polqual, pol.polrelid) || ')' || E'\n'
ELSE '' END ||
CASE WHEN pol.polwithcheck IS NOT NULL THEN
' WITH CHECK (' || pg_get_expr(pol.polwithcheck, pol.polrelid) || ')' || E'\n'
ELSE '' END || ';' AS ddl,
md5('CREATE POLICY ' || pol.polname || ' ON ' || n.nspname || '.' || c.relname) AS object_checksum,
'ROW LEVEL SECURITY' AS object_type,
10 AS sort_order
FROM pg_policy pol
JOIN pg_class c ON c.oid = pol.polrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND config.include_rls
),
table_comments AS (
SELECT
n.nspname,
c.relname AS object_name,
NULL AS table_name,
NULL AS other_name,
'COMMENT ON TABLE ' || n.nspname || '.' || c.relname ||
' IS ' || quote_literal(d.description) || ';' AS ddl,
md5('COMMENT ON TABLE ' || n.nspname || '.' || c.relname) AS object_checksum,
'TABLE COMMENT' AS object_type,
11 AS sort_order
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND c.relkind = 'r'
AND d.description IS NOT NULL
AND config.include_table_comments
),
op_families_ddl AS (
SELECT
n.nspname,
opf.opfname AS object_name,
NULL AS table_name,
am.amname AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP OPERATOR FAMILY ' || n.nspname || '.' || opf.opfname || ' USING ' || am.amname || ';' || E'\n'
ELSE ''
END ||
'CREATE OPERATOR FAMILY ' || n.nspname || '.' || opf.opfname || ' USING ' || am.amname || ';' || E'\n' ||
'ALTER OPERATOR FAMILY ' || n.nspname || '.' || opf.opfname || ' USING ' || am.amname || ' OWNER TO ' ||
pg_get_userbyid(opf.opfowner) || ';' AS ddl,
md5('CREATE OPERATOR FAMILY ' || n.nspname || '.' || opf.opfname) AS object_checksum,
'OPERATOR FAMILY' AS object_type,
15 AS sort_order
FROM pg_opfamily opf
JOIN pg_namespace n ON n.oid = opf.opfnamespace
JOIN pg_am am ON am.oid = opf.opfmethod
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND config.include_extensions
),
op_classes_ddl AS (
SELECT
n.nspname,
opc.opcname AS object_name,
NULL AS table_name,
am.amname AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP OPERATOR CLASS ' || n.nspname || '.' || opc.opcname || ' USING ' || am.amname || ';' || E'\n'
ELSE ''
END ||
'CREATE OPERATOR CLASS ' || n.nspname || '.' || opc.opcname || E'\n' ||
' DEFAULT FOR TYPE ' || format_type(opc.opcintype, NULL) || ' USING ' || am.amname || ' AS' || E'\n' ||
(SELECT string_agg(
CASE
WHEN amop.amopopr::regoperator IS NOT NULL THEN ' OPERATOR ' || amop.amopstrategy || ' ' || amop.amopopr::regoperator
WHEN amproc.amprocnum = 1 THEN ' FUNCTION 1 ' || amproc.amproc::regprocedure
ELSE ' FUNCTION ' || amproc.amprocnum || ' ' || amproc.amproc::regprocedure
END, ',' || E'\n'
) FROM
(SELECT * FROM pg_amop WHERE amopfamily = opc.opcfamily) amop
FULL JOIN
(SELECT * FROM pg_amproc WHERE amprocfamily = opc.opcfamily) amproc
ON amop.amopfamily = amproc.amprocfamily
) || ';' || E'\n' ||
'ALTER OPERATOR CLASS ' || n.nspname || '.' || opc.opcname || ' USING ' || am.amname || ' OWNER TO ' ||
pg_get_userbyid(opc.opcowner) || ';' AS ddl,
md5('CREATE OPERATOR CLASS ' || n.nspname || '.' || opc.opcname) AS object_checksum,
'OPERATOR CLASS' AS object_type,
16 AS sort_order
FROM pg_opclass opc
JOIN pg_namespace n ON n.oid = opc.opcnamespace
JOIN pg_am am ON am.oid = opc.opcmethod
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND config.include_extensions
),
dependency_graph AS (
SELECT
tc.table_name AS child_table,
ccu.table_name AS parent_table
FROM
information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema = (SELECT schema_name FROM config)
),
load_order AS (
SELECT
table_name,
MAX(array_length(path, 1)) AS dependency_depth
FROM (
WITH RECURSIVE dep_path AS (
SELECT
table_name,
ARRAY[table_name] AS path
FROM (SELECT DISTINCT child_table AS table_name FROM dependency_graph) t
UNION ALL
SELECT
dg.child_table,
dp.path || dg.child_table
FROM
dependency_graph dg
JOIN dep_path dp ON dg.parent_table = dp.table_name
WHERE
NOT dg.child_table = ANY(dp.path)
)
SELECT * FROM dep_path
) paths
GROUP BY table_name
),
aggregates_ddl AS (
SELECT
n.nspname,
p.proname || '(' || oidvectortypes(p.proargtypes) || ')' AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP AGGREGATE ' || n.nspname || '.' || p.proname || '(' || oidvectortypes(p.proargtypes) || ');' || E'\n'
ELSE ''
END ||
'CREATE AGGREGATE ' || n.nspname || '.' || p.proname || '(' || E'\n' ||
' BASETYPE = ' || (SELECT format_type(unnest(p.proargtypes), NULL) LIMIT 1) || ',' || E'\n' ||
' SFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggtransfn) || ',' || E'\n' ||
' STYPE = ' || format_type(a.aggtranstype, NULL) || ',' || E'\n' ||
CASE WHEN a.aggfinalfn <> 0 THEN ' FINALFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggfinalfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.agginitval IS NOT NULL THEN ' INITCOND = ' || quote_literal(a.agginitval) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggcombinefn <> 0 THEN ' COMBINEFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggcombinefn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggserialfn <> 0 THEN ' SERIALFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggserialfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggdeserialfn <> 0 THEN ' DESERIALFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggdeserialfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggmtransfn <> 0 THEN ' MSFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggmtransfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggminvtransfn <> 0 THEN ' MINVFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggminvtransfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggmfinalfn <> 0 THEN ' MFINALFUNC = ' || (SELECT proname FROM pg_proc WHERE oid = a.aggmfinalfn) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggmfinalfn <> 0 THEN ' MFINALFUNC_EXTRA = ' || (SELECT a.aggmfinalextra::text) || ',' || E'\n' ELSE '' END ||
CASE WHEN a.aggminitval IS NOT NULL THEN ' MINITCOND = ' || quote_literal(a.aggminitval) || ',' || E'\n' ELSE '' END ||
' SORTOP = ' || COALESCE((SELECT oprname FROM pg_operator WHERE oid = a.aggsortop)::text, 'NULL') || E'\n' ||
');' || E'\n' ||
'ALTER AGGREGATE ' || n.nspname || '.' || p.proname || '(' || oidvectortypes(p.proargtypes) || ') OWNER TO ' ||
pg_get_userbyid(p.proowner) || ';' AS ddl,
md5('CREATE AGGREGATE ' || n.nspname || '.' || p.proname) AS object_checksum,
'AGGREGATE' AS object_type,
17 AS sort_order
FROM pg_proc p
JOIN pg_aggregate a ON a.aggfnoid = p.oid
JOIN pg_namespace n ON n.oid = p.pronamespace
CROSS JOIN config
WHERE n.nspname = config.schema_name
AND p.prokind = 'a'
AND config.include_functions
),
publications_ddl AS (
SELECT
NULL AS nspname,
p.pubname AS object_name,
NULL AS table_name,
NULL AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP PUBLICATION ' || p.pubname || ';' || E'\n'
ELSE ''
END ||
'CREATE PUBLICATION ' || p.pubname || E'\n' ||
CASE WHEN p.puballtables THEN ' FOR ALL TABLES' || E'\n'
ELSE ' FOR TABLE ' || (
SELECT string_agg(pt.schemaname || '.' || pt.tablename, ', ')
FROM pg_publication_tables pt
WHERE pt.pubname = p.pubname
) || E'\n'
END ||
CASE WHEN p.pubinsert THEN ' WITH (publish = ''insert'')' || E'\n'
WHEN p.pubupdate THEN ' WITH (publish = ''update'')' || E'\n'
WHEN p.pubdelete THEN ' WITH (publish = ''delete'')' || E'\n'
WHEN p.pubtruncate THEN ' WITH (publish = ''truncate'')' || E'\n'
ELSE ''
END || ';' || E'\n' ||
'ALTER PUBLICATION ' || p.pubname || ' OWNER TO ' || pg_get_userbyid(p.pubowner) || ';' AS ddl,
md5('CREATE PUBLICATION ' || p.pubname) AS object_checksum,
'PUBLICATION' AS object_type,
18 AS sort_order
FROM pg_publication p
CROSS JOIN config
WHERE config.include_extensions
),
subscriptions_ddl AS (
SELECT
NULL AS nspname,
s.subname AS object_name,
NULL AS table_name,
(SELECT string_agg(option_name || '=' || option_value, ', ')
FROM pg_options_to_table(s.subconninfo::text[])) AS other_name,
CASE WHEN config.include_drop_statements
THEN '-- DROP SUBSCRIPTION ' || s.subname || ';' || E'\n'
ELSE ''
END ||
'CREATE SUBSCRIPTION ' || s.subname || E'\n' ||
' CONNECTION ' || quote_literal(s.subconninfo) || E'\n' ||
' PUBLICATION ' || (
SELECT string_agg(p.pubname, ', ')
FROM pg_publication p
WHERE p.oid::text = ANY(s.subpublications::text[])
) || E'\n' ||
CASE WHEN s.subenabled THEN ' WITH (enabled)' || E'\n'
ELSE ' WITH (disabled)' || E'\n'
END || ';' || E'\n' ||
'ALTER SUBSCRIPTION ' || s.subname || ' OWNER TO ' || pg_get_userbyid(s.subowner) || ';' AS ddl,
md5('CREATE SUBSCRIPTION ' || s.subname) AS object_checksum,
'SUBSCRIPTION' AS object_type,
19 AS sort_order
FROM pg_subscription s
CROSS JOIN config
WHERE config.include_extensions
),
all_objects AS (
SELECT * FROM table_ddls
UNION ALL SELECT * FROM views_ddl
UNION ALL SELECT * FROM matviews_ddl
UNION ALL SELECT * FROM sequences_ddl
UNION ALL SELECT * FROM functions_ddl
UNION ALL SELECT * FROM aggregates_ddl
UNION ALL SELECT * FROM extensions_ddl
UNION ALL SELECT * FROM table_comments
UNION ALL SELECT * FROM triggers_ddl
UNION ALL SELECT * FROM domains_ddl
UNION ALL SELECT * FROM rules_ddl
UNION ALL SELECT * FROM rls_ddl
UNION ALL SELECT * FROM collations_ddl
UNION ALL SELECT * FROM casts_ddl
UNION ALL SELECT * FROM operators_ddl
UNION ALL SELECT * FROM op_families_ddl
UNION ALL SELECT * FROM op_classes_ddl
UNION ALL SELECT * FROM publications_ddl
UNION ALL SELECT * FROM subscriptions_ddl
UNION ALL SELECT * FROM fdw_ddl
UNION ALL SELECT * FROM foreign_servers_ddl
),
database_signature AS (
SELECT
0 AS sort_priority,
'-- DATABASE SCHEMA SIGNATURE' || E'\n' ||
'-- Generated: ' || now() AT TIME ZONE 'UTC' || E'\n' ||
'-- Schema: ' || config.schema_name || E'\n' ||
'-- Total objects: ' || COUNT(*) || E'\n' ||
'-- Overall checksum: ' || md5(string_agg(object_checksum::text, '' ORDER BY sort_order, nspname, object_name)) || E'\n' ||
'-- Dependency depth: ' || COALESCE(MAX(lo.dependency_depth), 0) AS ddl
FROM all_objects ao
LEFT JOIN load_order lo ON ao.object_name = lo.table_name
CROSS JOIN config
WHERE config.include_checksums
GROUP BY config.schema_name
)
SELECT
CASE WHEN config.include_checksums
THEN '-- Object checksum: ' || object_checksum || E'\n'
ELSE ''
END ||
CASE WHEN lo.dependency_depth IS NOT NULL AND object_type = 'TABLE'
THEN '-- Dependency depth: ' || lo.dependency_depth || E'\n'
ELSE ''
END ||
ddl AS ddl,
sort_order AS sort_priority,
object_type,
nspname,
object_name,
COALESCE(lo.dependency_depth, 0) AS dependency_depth
FROM all_objects ao
LEFT JOIN load_order lo ON ao.object_name = lo.table_name
CROSS JOIN config
WHERE (config.exclude_objects IS NULL OR ao.object_name NOT IN (SELECT unnest(config.exclude_objects)))
UNION ALL
SELECT
CASE WHEN config.include_checksums
THEN '-- Object checksum: ' || object_checksum || E'\n'
ELSE ''
END ||
CASE WHEN lo.dependency_depth IS NOT NULL AND object_type = 'TABLE'
THEN '-- Dependency depth: ' || lo.dependency_depth || E'\n'
ELSE ''
END ||
ddl AS ddl,
sort_order AS sort_priority,
object_type,
nspname,
object_name,
COALESCE(lo.dependency_depth, 0) AS dependency_depth
FROM all_objects ao
LEFT JOIN load_order lo ON ao.object_name = lo.table_name
CROSS JOIN config
WHERE (config.exclude_objects IS NULL OR ao.object_name NOT IN (SELECT unnest(config.exclude_objects))
SELECT * FROM (
WITH all_results AS (
SELECT
CASE WHEN config.include_checksums
THEN '-- Object checksum: ' || object_checksum || E'\n'
ELSE ''
END ||
CASE WHEN lo.dependency_depth IS NOT NULL AND object_type = 'TABLE'
THEN '-- Dependency depth: ' || lo.dependency_depth || E'\n'
ELSE ''
END ||
ddl AS ddl,
sort_order AS sort_priority,
object_type,
nspname,
object_name,
COALESCE(lo.dependency_depth, 0) AS dependency_depth,
1 AS result_type
FROM all_objects ao
LEFT JOIN load_order lo ON ao.object_name = lo.table_name
CROSS JOIN config
WHERE (config.exclude_objects IS NULL OR ao.object_name NOT IN (SELECT unnest(config.exclude_objects)))
UNION ALL
SELECT
ddl,
sort_priority,
NULL AS object_type,
NULL AS nspname,
NULL AS object_name,
0 AS dependency_depth,
2 AS result_type
FROM database_signature
)
SELECT ddl
FROM all_results
ORDER BY
result_type,
sort_priority,
CASE
WHEN object_type = 'EXTENSION' THEN 1
WHEN object_type = 'FOREIGN DATA WRAPPER' THEN 2
WHEN object_type = 'FOREIGN SERVER' THEN 3
WHEN object_type = 'COLLATION' THEN 4
WHEN object_type = 'DOMAIN' THEN 5
WHEN object_type = 'TABLE' THEN 6
WHEN object_type = 'PARTITIONED TABLE' THEN 7
WHEN object_type = 'TABLE PARTITION' THEN 8
WHEN object_type = 'SEQUENCE' THEN 9
WHEN object_type = 'FUNCTION' THEN 10
WHEN object_type = 'AGGREGATE' THEN 11
WHEN object_type = 'OPERATOR' THEN 12
WHEN object_type = 'OPERATOR FAMILY' THEN 13
WHEN object_type = 'OPERATOR CLASS' THEN 14
WHEN object_type = 'VIEW' THEN 15
WHEN object_type = 'MATERIALIZED VIEW' THEN 16
WHEN object_type = 'RULE' THEN 17
WHEN object_type = 'TRIGGER' THEN 18
WHEN object_type = 'ROW LEVEL SECURITY' THEN 19
WHEN object_type = 'CAST' THEN 20
WHEN object_type = 'PUBLICATION' THEN 21
WHEN object_type = 'SUBSCRIPTION' THEN 22
WHEN object_type = 'TABLE COMMENT' THEN 23
ELSE 24
END,
dependency_depth DESC,
nspname NULLS FIRST,
object_name
) AS final_results;
Telegram: DB developers channel