| From: | Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements |
| Date: | 2026-06-03 12:58:52 |
| Message-ID: | CANxoLDfjQnhM=E6JSyYo9s9OdjqoN8s_3wE5yL=kaDu_X8j-dA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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)
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch | application/octet-stream | 86.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Andres Freund | 2026-06-03 12:53:10 | Re: Heads Up: cirrus-ci is shutting down June 1st |