[PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

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

Browse pgsql-hackers by date

  From Date Subject
Previous Message Andres Freund 2026-06-03 12:53:10 Re: Heads Up: cirrus-ci is shutting down June 1st