pg_dbms_metadata

pig build pkg pg_dbms_metadata;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_dbms_metadata;		# install via package name, for the active PG version

pig install pg_dbms_metadata -v 18;   # install for PG 18
pig install pg_dbms_metadata -v 17;   # install for PG 17
pig install pg_dbms_metadata -v 16;   # install for PG 16
pig install pg_dbms_metadata -v 15;   # install for PG 15
pig install pg_dbms_metadata -v 14;   # install for PG 14

CREATE EXTENSION pg_dbms_metadata;

CREATE EXTENSION pg_dbms_metadata;

SELECT dbms_metadata.get_ddl('TABLE', 'employees', 'public');
SELECT dbms_metadata.get_ddl('VIEW', 'active_users', 'myschema');
SELECT dbms_metadata.get_ddl('FUNCTION', 'calculate_tax', 'public');
SELECT dbms_metadata.get_ddl('INDEX', 'idx_name', 'public');

-- Schema is optional; uses search_path when omitted
SELECT dbms_metadata.get_ddl('SEQUENCE', 'my_seq');

SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT', 'employees', 'public');
SELECT dbms_metadata.get_dependent_ddl('INDEX', 'orders', 'sales');
SELECT dbms_metadata.get_dependent_ddl('TRIGGER', 'accounts');

SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'user_test');

-- Append SQL terminator (;) to each DDL statement
CALL dbms_metadata.set_transform_param('SQLTERMINATOR', true);

-- Exclude constraints from TABLE DDL
CALL dbms_metadata.set_transform_param('CONSTRAINTS', false);

-- Exclude foreign keys from TABLE DDL
CALL dbms_metadata.set_transform_param('REF_CONSTRAINTS', false);

-- Exclude partitioning clauses
CALL dbms_metadata.set_transform_param('PARTITIONING', false);

-- Exclude storage parameters
CALL dbms_metadata.set_transform_param('STORAGE', false);

-- Reset all params to defaults
CALL dbms_metadata.set_transform_param('DEFAULT', true);