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

Hi Hackers,

Attached is the v2 patch, which fixes the Meson build failure, and the
rebased patch.

On Wed, Jun 3, 2026 at 6:28 PM Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> 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)
>
>