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

On Tue, Jun 16, 2026 at 2:35 AM Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:

> Em seg., 15 de jun. de 2026 às 04:52, Akshay Joshi <
> akshay(dot)joshi(at)enterprisedb(dot)com> escreveu:
>
>> I don't think per-contype flags are the right shape, though. The existing
>> toggles group by catalog (indexes, constraints, rules, ...); splitting
>> constraints into PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a
>> second axis, and the function already carries nine. Only FKs have the
>> cross-table dependency-ordering problem; the rest only reference the same
>> table, so splitting them unlocks nothing new.
>>
>
> Ok, I understand your point. Initially, I saw the usefulness of this
> function for cloning a schema, something very common in a multi-tenant
> model. But creating the foreign keys along with the create table makes that
> unfeasible.
>
> Options are variadic, so you could split your emit_local_constraints into
> +emit_local_foreign_keys_constraints(TableDdlContext * ctx)
> + if (!(ctx->include_constraints || ctx->include_foreign_keys)) then
> + return
>
> +emit_local_primary_keys_constraints(TableDdlContext * ctx)
> + if (!(ctx->include_constraints || ctx->include_primary_keys)) then
> + return
>
> pg_get_table_ddl('x','includes_constraints','true') -- would print all
> constraints
> pg_get_table_ddl('x','include_primary_keys','true') -- would print only
> primary key constraints
>

The schema cloning use case is valid. The v7 patch (attached) adds a single
new option, `includes_foreign_keys` (boolean, default true), which acts as
an additive gate underneath `includes_constraints`. Calling
pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits everything
except FOREIGN KEY constraints. This covers the multi-tenant clone
workflow: create tables first without cross-table references, then re-run
with the default to add the constraints once all targets exist.

I held off on the broader split into per-contype options
(includes_primary_keys, includes_unique, etc.) for two reasons. First, only
FOREIGN KEY actually breaks schema cloning. PRIMARY KEY, UNIQUE, CHECK,
EXCLUDE, and named NOT NULL constraints are all table local and don't
reference anything else, so no workflow currently needs to suppress them
independently. Second, the function already exposes thirteen boolean
options; adding five more granular ones without a concrete use case expands
the surface area unnecessarily.

The v7 patch is ready for review.

>
> regards
> Marcos
>