Hackers, Attached is a patch adding pg_get_table_ddl(regclass, VARIADIC text[]), extending the existing pg_get_database/role/tablespace_ddl family. relations. It returns the CREATE TABLE statement plus the follow-up ALTER TABLE, CREATE INDEX, CREATE RULE, and CREATE STATISTICS statements needed for a full reconstruction, one per row.
*Coverage* -------- *Per-column:* typmod, COLLATE, STORAGE, COMPRESSION (pglz/lz4), GENERATED STORED/VIRTUAL, IDENTITY ALWAYS/BY DEFAULT (with non-default sequence options), DEFAULT, NOT NULL, attoptions. *Table-level:* UNLOGGED, INHERITS, PARTITION BY (RANGE/LIST/HASH), PARTITION OF ... FOR VALUES, USING access method, WITH (reloptions), TABLESPACE, inline CHECK. *Sub-objects:* Indexes, Constraints (PK/UNIQUE/FK/EXCLUDE/named NOT NULL), Rules, extended statistics, REPLICA IDENTITY, ENABLE/FORCE RLS, and child-local DEFAULT overrides. *Triggers and policies are TODOs pending pg_get_trigger_ddl (Phil's re-roll) and pg_get_policy_ddl (Waiting for review/commit)*. Options (pretty, owner, tablespace, and a family of *includes_** toggles for each sub-object class) let callers fine-tune the output. Every clause is omitted when its value equals what the system would reapply on round-trip same default-omission convention as the existing _ddl functions. I deliberately did *not* add pg_get_index_ddl / _constraint_ddl / _rule_ddl / _stat_ddl wrappers. The existing C helpers in ruleutils.c (pg_get_indexdef_string, pg_get_constraintdef_command, etc.) already emit reproducible statements, so pg_get_table_ddl_internal calls them directly. Happy to revisit if reviewers prefer separate SQL-level surfaces. *Testing: * pg_regress test at src/test/regress/sql/pg_get_table_ddl.sql covering the matrix above plus error paths. I kept it as pg_regress rather than TAP (where the sibling _ddl tests live) since exact-.out diffs are the more rigorous check for a deparse function, and the pg_regress restriction that drove the TAP choice for CREATE DATABASE/TABLESPACE doesn't apply to tables. A manual round-trip across IDENTITY/GENERATED/ STORAGE/COMPRESSION/PK produces an empty EXCEPT diff against the original. *Out of scope:* COMMENT ON, GRANT/REVOKE (matching the existing _ddl family), and typed tables (CREATE TABLE name OF type). Feedback is welcome, particularly on the option naming and the decision to reuse the C helpers directly rather than add new SQL wrappers. ----- Regards, Akshay Joshi Principal Engineer | Engineering Manager | pgAdmin Hacker EDB (EnterpriseDB)
v1-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch
Description: Binary data
