public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Kyotaro Horiguchi <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements
Date: Tue, 23 Jun 2026 19:04:25 +0530
Message-ID: <CANxoLDdEBYzKfj3NGrP5PjoTa-eCViWPkFR-sXLq4bUnbokyPA@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDdq06oXRYuzYfn2gkn3yfoxp=nA14xu5p+qSEOzpHXEMQ@mail.gmail.com>
References: <CANxoLDejn2JFXF7kVMtcznW6XyessrJ7WToSaQau2GxBdAGSUg@mail.gmail.com>
<[email protected]>
<CANxoLDe_GOXyyZs7GN3VUJoT+o1DwqZGhid-TWWvS0D8d5ggYw@mail.gmail.com>
<[email protected]>
<CANxoLDdq06oXRYuzYfn2gkn3yfoxp=nA14xu5p+qSEOzpHXEMQ@mail.gmail.com>
All,
A quick note on a design change I made in the *pg_get_table_ddl* option
surface.
The earlier draft exposed one boolean option per sub-object kind:
include_indexes, include_primary_key, include_check, include_foreign_keys,
include_rules, include_statistics, include_rls, include_replica_identity,
include_partitions, and so on. Every new kind we wanted to gate (triggers,
policies, exclusion constraints…) meant another option name. Callers
wanting "everything except FKs" had to flip nine flags, and the
include/exclude polarity was not symmetric: to drop one item, you toggled
one flag, but to keep only one item, you toggled all the others.
*I have replaced those with two options:*
- include — comma-separated list of kinds; emit only the listed ones.
- exclude — comma-separated list of kinds; emit everything except the
listed ones.
*Vocabulary*: table, indexes, primary_key, unique, check, foreign_keys,
exclusion, rules, statistics, triggers, policies, rls, replica_identity,
partitions.
NOT NULL is intentionally omitted from the vocabulary; it's always emitted
to avoid silently producing schemas that accept NULLs when the source would
have rejected them.
The v10 patch is ready for review.
On Tue, Jun 23, 2026 at 2:52 PM Akshay Joshi <[email protected]>
wrote:
>
>
> On Tue, Jun 23, 2026 at 12:51 PM Kyotaro Horiguchi <
> [email protected]> wrote:
>
>> At Mon, 22 Jun 2026 18:10:53 +0530, Akshay Joshi <
>> [email protected]> wrote in
>> > The v9 patch is ready for review.
>>
>> I have not looked closely at the DDL generation logic itself, but I
>> have a few comments on how pg_get_table_ddl handles its options.
>>
>> Since pg_get_table_ddl_internal() appears to copy these values into
>> TableDdlContext almost immediately, I wonder whether TableDdlContext
>> could be initialized by the caller instead.
>>
>> Using positional boolean arguments is probably fine when there are
>> only a handful of options, but with around fifteen of them the current
>> approach seems somewhat error-prone.
>>
>> It might also be clearer to initialize the default values first, and
>> then override only the fields corresponding to explicitly specified
>> options, rather than folding the default handling and option lookup
>> into the same expression.
>>
>
> I assume that changing the implementation model, as I mentioned in my
> other email, will solve this problem as well. We can drop the entire
> includes_* family and only_foreign_keys, and replace them with two mutually
> exclusive variadic keys:
> include => 'kind1,kind2,...' — emit only these kinds
> exclude => 'kind1,kind2,...' — emit everything except these kinds
>>
>>
>> Regards,
>>
>> --
>> Kyotaro Horiguchi
>> NTT Open Source Software Center
>>
>
Attachments:
[application/octet-stream] v10-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch (149.6K, ../CANxoLDdEBYzKfj3NGrP5PjoTa-eCViWPkFR-sXLq4bUnbokyPA@mail.gmail.com/3-v10-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch)
download | inline diff:
From 07d30797beb4aab778f46fa1dabcebaba5250f23 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Tue, 2 Jun 2026 14:18:40 +0530
Subject: [PATCH v10] Add pg_get_table_ddl() to reconstruct CREATE TABLE
statements
The function reconstructs the CREATE TABLE statement for an ordinary or
partitioned table, followed by the ALTER TABLE / CREATE INDEX /
CREATE RULE / CREATE STATISTICS statements needed to restore its full
definition. Each statement is returned as a separate row.
Supported per-column features: data type with type modifiers, COLLATE,
STORAGE, COMPRESSION (pglz / lz4), GENERATED ALWAYS AS (expr)
STORED/VIRTUAL, GENERATED ALWAYS|BY DEFAULT AS IDENTITY (with sequence
options), DEFAULT, NOT NULL, and per-column attoptions emitted as
ALTER COLUMN SET (...).
Supported table-level features: UNLOGGED, INHERITS, PARTITION BY (RANGE
/ LIST / HASH parents), PARTITION OF parent FOR VALUES (FROM/TO, WITH
modulus/remainder, DEFAULT), USING table access method, WITH
(reloptions), TABLESPACE, and inline CHECK constraints in the
CREATE TABLE body.
Supported sub-objects (re-using existing deparse helpers from
ruleutils.c): indexes via pg_get_indexdef_string, constraints
(PRIMARY KEY, UNIQUE, FOREIGN KEY, EXCLUDE, named NOT NULL) via
pg_get_constraintdef_command, rules via pg_get_ruledef, extended
statistics via pg_get_statisticsobjdef_string, REPLICA IDENTITY
NOTHING/FULL/USING INDEX, ALTER TABLE ENABLE/FORCE ROW LEVEL SECURITY,
and child-local DEFAULT overrides on inheritance/partition children.
DDL for partition children of a partitioned-table parent is appended
after the parent by default.
Options are passed as variadic name/value pairs. Formatting options
are pretty (boolean), owner (boolean, controls the
ALTER TABLE ... OWNER TO line), tablespace (boolean, controls the
TABLESPACE clause), and schema_qualified (boolean, controls whether
the target table and same-schema sibling references are emitted with
their schema prefix). Object-class filtering is expressed through two
mutually-exclusive options, include and exclude, each taking a
comma-separated list drawn from the kind vocabulary
{table, indexes, primary_key, unique, check, foreign_keys, exclusion,
rules, statistics, triggers, policies, rls, replica_identity,
partitions}. When include is set, only the listed kinds are emitted;
when exclude is set, every kind except the listed ones is emitted;
when neither is set, every kind is emitted. Unknown kind names raise
an error at parse time. NOT NULL is deliberately not part of the
vocabulary - it is always emitted to prevent producing schemas that
silently accept NULLs the source would have rejected. Excluding
primary_key on a table whose REPLICA IDENTITY is USING INDEX of the
primary key is rejected unless replica_identity is also excluded, so
the emitted DDL never references an index it just dropped.
Default omission convention: every optional clause is dropped when its
value equals what the system would reapply on round-trip, including
type-default COLLATE, per-type STORAGE, the auto-generated identity
sequence name and parameter defaults, heap access method, default
REPLICA IDENTITY, disabled RLS toggles, empty reloptions, and the
default tablespace.
Author: Akshay Joshi <[email protected]>
Reviewed-by: Marcos Pegoraro <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Kyotaro Horiguchi <[email protected]>
Reviewed-by: Chao Li <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 120 +
src/backend/catalog/pg_inherits.c | 93 +
src/backend/commands/tablecmds.c | 30 +-
src/backend/utils/adt/ddlutils.c | 1996 +++++++++++++++++
src/include/catalog/pg_inherits.h | 1 +
src/include/catalog/pg_proc.dat | 7 +
src/include/commands/tablecmds.h | 3 +
.../regress/expected/pg_get_table_ddl.out | 885 ++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/pg_get_table_ddl.sql | 542 +++++
10 files changed, 3676 insertions(+), 3 deletions(-)
create mode 100644 src/test/regress/expected/pg_get_table_ddl.out
create mode 100644 src/test/regress/sql/pg_get_table_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 8ffa7e83275..d1759dbde67 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3947,6 +3947,126 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_table_ddl</primary>
+ </indexterm>
+ <function>pg_get_table_ddl</function>
+ ( <parameter>table</parameter> <type>regclass</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE TABLE</command> statement for the
+ specified ordinary or partitioned table, followed by the
+ <command>ALTER TABLE</command>, <command>CREATE INDEX</command>,
+ <command>CREATE RULE</command>, and <command>CREATE STATISTICS</command>
+ statements needed to recreate the table's columns, constraints,
+ indexes, rules, extended statistics, and row-level security flags.
+ Inherited columns and constraints are emitted by the parent table's
+ DDL and are not duplicated on inheritance children or partitions.
+ Each statement is returned as a separate row.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output,
+ <literal>owner</literal> (boolean) to include the
+ <command>ALTER TABLE ... OWNER TO</command> statement,
+ <literal>tablespace</literal> (boolean) to include the
+ <literal>TABLESPACE</literal> clause on the
+ <command>CREATE TABLE</command> statement, and the
+ <literal>include</literal> and <literal>exclude</literal>
+ options described below for filtering which object classes are
+ emitted.
+ </para>
+ <para>
+ The <literal>include</literal> and <literal>exclude</literal>
+ options each take a comma-separated list of object-class kind
+ names and are mutually exclusive (specifying both raises an
+ error). When <literal>include</literal> is set, only the
+ listed kinds are emitted; when <literal>exclude</literal> is
+ set, every kind <emphasis>except</emphasis> the listed ones is
+ emitted. When neither is set (the default), every kind is
+ emitted. Whitespace around each entry is ignored and matching
+ is case-insensitive; an unrecognized kind name raises an error.
+ The kind vocabulary is
+ <literal>table</literal>,
+ <literal>indexes</literal>,
+ <literal>primary_key</literal>,
+ <literal>unique</literal>,
+ <literal>check</literal>,
+ <literal>foreign_keys</literal>,
+ <literal>exclusion</literal>,
+ <literal>rules</literal>,
+ <literal>statistics</literal>,
+ <literal>triggers</literal>,
+ <literal>policies</literal>,
+ <literal>rls</literal> (the
+ <command>ENABLE</command>/<command>FORCE ROW LEVEL SECURITY</command>
+ toggles),
+ <literal>replica_identity</literal>, and
+ <literal>partitions</literal> (the DDL for each direct
+ partition child of a partitioned-table parent). The
+ <literal>table</literal> kind groups the
+ <command>CREATE TABLE</command> statement together with the
+ related per-table <command>ALTER TABLE</command> passes:
+ <command>OWNER TO</command>, child-default
+ <command>SET DEFAULT</command>, and per-column
+ <command>SET (<replaceable>attoptions</replaceable>)</command>.
+ <literal>NOT NULL</literal> constraints are not part of the
+ vocabulary; they are always emitted as part of the table to
+ avoid producing schemas that silently accept
+ <literal>NULL</literal> values the source would have rejected.
+ For example, the second pass of a two-pass schema clone that
+ adds cross-table foreign keys after data has loaded can be
+ written as
+ <literal>include => 'foreign_keys'</literal>, and the
+ pub/sub-style clone that keeps a primary key but drops every
+ other constraint can be written as
+ <literal>exclude => 'unique,check,foreign_keys,exclusion'</literal>.
+ </para>
+ <para>
+ Excluding <literal>primary_key</literal> on a table whose
+ <literal>REPLICA IDENTITY</literal> is set to
+ <literal>USING INDEX</literal> of the primary key raises an
+ error unless <literal>replica_identity</literal> is also
+ excluded; otherwise the emitted DDL would reference an index
+ the same DDL just dropped.
+ </para>
+ <para>
+ The <literal>schema_qualified</literal> option (boolean, default
+ <literal>true</literal>) controls whether the target table's own
+ schema is included in the generated DDL. When set to
+ <literal>false</literal>, the table name is emitted unqualified
+ in the <command>CREATE TABLE</command> and every subsequent
+ <command>ALTER TABLE</command>, <command>CREATE INDEX</command>,
+ <command>CREATE RULE</command>, and
+ <command>CREATE STATISTICS</command> statement. References to
+ objects in the same schema as the target table (inheritance
+ parents, partition parents, identity sequences, and any
+ same-schema object the deparse helpers happen to mention) are
+ also emitted unqualified, so the script can be replayed under a
+ different <varname>search_path</varname> to recreate the table
+ in another schema. Cross-schema references (for example a
+ foreign key target in a different schema) remain qualified for
+ correctness.
+ </para>
+ <para>
+ All three forms of <command>CREATE TABLE</command> are supported:
+ the ordinary column-list form, the typed-table form
+ (<literal>OF <replaceable>type_name</replaceable></literal>, with
+ per-column <literal>WITH OPTIONS</literal> overrides for local
+ defaults, <literal>NOT NULL</literal>, and <literal>CHECK</literal>
+ constraints), and the <literal>PARTITION OF</literal> form.
+ <literal>TEMPORARY</literal> and <literal>UNLOGGED</literal>
+ persistence modes are emitted from
+ <structfield>relpersistence</structfield>. For temporary tables
+ registered in the current session, the
+ <literal>ON COMMIT DELETE ROWS</literal> and
+ <literal>ON COMMIT DROP</literal> clauses are emitted; the default
+ <literal>ON COMMIT PRESERVE ROWS</literal> is omitted.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/pg_inherits.c b/src/backend/catalog/pg_inherits.c
index 4b9802aafcc..897151bd6d1 100644
--- a/src/backend/catalog/pg_inherits.c
+++ b/src/backend/catalog/pg_inherits.c
@@ -236,6 +236,99 @@ find_inheritance_children_extended(Oid parentrelId, bool omit_detached,
}
+/*
+ * find_inheritance_parents
+ *
+ * Returns a list containing the OIDs of all relations that the relation with
+ * OID 'relid' inherits *directly* from, in inhseqno order (the order in which
+ * they should appear in an INHERITS clause).
+ *
+ * The specified lock type is acquired on each parent relation (but not on the
+ * given rel; caller should already have locked it). If lockmode is NoLock
+ * then no locks are acquired, but caller must beware of race conditions
+ * against possible DROPs of parent relations.
+ *
+ * Partition children also have a pg_inherits entry pointing to the
+ * partitioned parent; callers that distinguish INHERITS from PARTITION OF
+ * must check relispartition themselves.
+ */
+List *
+find_inheritance_parents(Oid relid, LOCKMODE lockmode)
+{
+ List *list = NIL;
+ Relation relation;
+ SysScanDesc scan;
+ ScanKeyData key[1];
+ HeapTuple inheritsTuple;
+ Oid inhparent;
+ Oid *oidarr;
+ int maxoids,
+ numoids,
+ i;
+
+ /*
+ * Scan pg_inherits via the (inhrelid, inhseqno) index so that the rows
+ * come out in inhseqno order, which is the order required for the
+ * INHERITS clause.
+ */
+ maxoids = 8;
+ oidarr = (Oid *) palloc(maxoids * sizeof(Oid));
+ numoids = 0;
+
+ relation = table_open(InheritsRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_inherits_inhrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relid));
+
+ scan = systable_beginscan(relation, InheritsRelidSeqnoIndexId, true,
+ NULL, 1, key);
+
+ while ((inheritsTuple = systable_getnext(scan)) != NULL)
+ {
+ inhparent = ((Form_pg_inherits) GETSTRUCT(inheritsTuple))->inhparent;
+ if (numoids >= maxoids)
+ {
+ maxoids *= 2;
+ oidarr = (Oid *) repalloc(oidarr, maxoids * sizeof(Oid));
+ }
+ oidarr[numoids++] = inhparent;
+ }
+
+ systable_endscan(scan);
+
+ table_close(relation, AccessShareLock);
+
+ /*
+ * Acquire locks and build the result list. Unlike
+ * find_inheritance_children we do *not* sort by OID: callers need the
+ * seqno-ordered traversal.
+ */
+ for (i = 0; i < numoids; i++)
+ {
+ inhparent = oidarr[i];
+
+ if (lockmode != NoLock)
+ {
+ LockRelationOid(inhparent, lockmode);
+
+ if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(inhparent)))
+ {
+ UnlockRelationOid(inhparent, lockmode);
+ continue;
+ }
+ }
+
+ list = lappend_oid(list, inhparent);
+ }
+
+ pfree(oidarr);
+
+ return list;
+}
+
+
/*
* find_all_inheritors -
* Returns a list of relation OIDs including the given rel plus
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 265dcfe7fda..a5f46522c33 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -739,7 +739,6 @@ static ObjectAddress ATExecSetCompression(Relation rel,
const char *column, Node *newValue, LOCKMODE lockmode);
static void index_copy_data(Relation rel, RelFileLocator newrlocator);
-static const char *storage_name(char c);
static void RangeVarCallbackForDropRelation(const RangeVar *rel, Oid relOid,
Oid oldRelOid, void *arg);
@@ -2515,7 +2514,7 @@ truncate_check_activity(Relation rel)
* storage_name
* returns the name corresponding to a typstorage/attstorage enum value
*/
-static const char *
+const char *
storage_name(char c)
{
switch (c)
@@ -19558,6 +19557,33 @@ remove_on_commit_action(Oid relid)
}
}
+/*
+ * Look up the registered ON COMMIT action for a relation.
+ *
+ * Returns ONCOMMIT_NOOP when nothing was registered, which also covers
+ * temporary tables created with the default ON COMMIT PRESERVE ROWS
+ * behavior (register_on_commit_action() skips those, since no action is
+ * needed at commit). Entries marked for deletion in the current
+ * transaction are ignored.
+ */
+OnCommitAction
+get_on_commit_action(Oid relid)
+{
+ ListCell *l;
+
+ foreach(l, on_commits)
+ {
+ OnCommitItem *oc = (OnCommitItem *) lfirst(l);
+
+ if (oc->relid != relid)
+ continue;
+ if (oc->deleting_subid != InvalidSubTransactionId)
+ continue;
+ return oc->oncommit;
+ }
+ return ONCOMMIT_NOOP;
+}
+
/*
* Perform ON COMMIT actions.
*
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..9e00061c62b 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -21,12 +21,25 @@
#include "access/genam.h"
#include "access/htup_details.h"
#include "access/table.h"
+#include "access/toast_compression.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_class.h"
#include "catalog/pg_collation.h"
+#include "catalog/pg_constraint.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_am.h"
+#include "catalog/pg_inherits.h"
+#include "catalog/pg_policy.h"
+#include "catalog/pg_sequence.h"
+#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_tablespace.h"
+#include "catalog/pg_trigger.h"
+#include "catalog/partition.h"
+#include "commands/defrem.h"
+#include "commands/tablecmds.h"
+#include "nodes/nodes.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
#include "funcapi.h"
@@ -73,8 +86,55 @@ typedef struct DdlOption
} DdlOption;
+/*
+ * Object-class kinds that the include/exclude options on
+ * pg_get_table_ddl can filter on. Members are stored as integers in a
+ * Bitmapset on TableDdlContext. Keep table_ddl_kind_names[] in sync
+ * with the order of additions here.
+ */
+typedef enum TableDdlKind
+{
+ TABLE_DDL_KIND_TABLE,
+ TABLE_DDL_KIND_INDEXES,
+ TABLE_DDL_KIND_PRIMARY_KEY,
+ TABLE_DDL_KIND_UNIQUE,
+ TABLE_DDL_KIND_CHECK,
+ TABLE_DDL_KIND_FOREIGN_KEYS,
+ TABLE_DDL_KIND_EXCLUSION,
+ TABLE_DDL_KIND_RULES,
+ TABLE_DDL_KIND_STATISTICS,
+ TABLE_DDL_KIND_TRIGGERS,
+ TABLE_DDL_KIND_POLICIES,
+ TABLE_DDL_KIND_RLS,
+ TABLE_DDL_KIND_REPLICA_IDENTITY,
+ TABLE_DDL_KIND_PARTITIONS,
+} TableDdlKind;
+
+static const struct
+{
+ const char *name;
+ TableDdlKind kind;
+} table_ddl_kind_names[] =
+{
+ {"table", TABLE_DDL_KIND_TABLE},
+ {"indexes", TABLE_DDL_KIND_INDEXES},
+ {"primary_key", TABLE_DDL_KIND_PRIMARY_KEY},
+ {"unique", TABLE_DDL_KIND_UNIQUE},
+ {"check", TABLE_DDL_KIND_CHECK},
+ {"foreign_keys", TABLE_DDL_KIND_FOREIGN_KEYS},
+ {"exclusion", TABLE_DDL_KIND_EXCLUSION},
+ {"rules", TABLE_DDL_KIND_RULES},
+ {"statistics", TABLE_DDL_KIND_STATISTICS},
+ {"triggers", TABLE_DDL_KIND_TRIGGERS},
+ {"policies", TABLE_DDL_KIND_POLICIES},
+ {"rls", TABLE_DDL_KIND_RLS},
+ {"replica_identity", TABLE_DDL_KIND_REPLICA_IDENTITY},
+ {"partitions", TABLE_DDL_KIND_PARTITIONS},
+};
+
static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
DdlOption *opts, int nopts);
+static Bitmapset *parse_kind_list(const char *optname, const char *list);
static void append_ddl_option(StringInfo buf, bool pretty, int indent,
const char *fmt, ...)
pg_attribute_printf(4, 5);
@@ -87,6 +147,95 @@ static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool i
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
+/*
+ * Per-column cache of locally-declared NOT NULL constraints. Built once
+ * by collect_local_not_null() and consulted by the column-emit helpers
+ * and the post-CREATE constraint loop. Entries with conoid == InvalidOid
+ * mean the column has no local NOT NULL constraint row in pg_constraint
+ * (the column may still have attnotnull=true on a pre-PG-18-upgraded
+ * catalog, in which case emit plain inline NOT NULL).
+ */
+typedef struct LocalNotNullEntry
+{
+ Oid conoid;
+ char *name;
+ bool is_auto; /* matches "<table>_<col>_not_null" */
+ bool no_inherit;
+} LocalNotNullEntry;
+
+/*
+ * Working context threaded through the per-pass helpers below. Inputs
+ * (caller-provided option flags) are filled in once at the top of
+ * pg_get_table_ddl_internal and treated as read-only thereafter. Derived
+ * fields (qualname, schema_prefix, nn_entries, skip_notnull_oids) are
+ * computed once during setup. Each pass appends to ctx->buf and pushes
+ * the finished statement onto ctx->statements via append_stmt().
+ */
+typedef struct TableDdlContext
+{
+ Relation rel;
+ Oid relid;
+ bool pretty;
+ bool no_owner;
+ bool no_tablespace;
+ bool schema_qualified;
+
+ /*
+ * Object-class filtering. If included_kinds is non-NULL, only the
+ * kinds in that set are emitted; if excluded_kinds is non-NULL, all
+ * kinds *except* those in that set are emitted; if both are NULL,
+ * every kind is emitted (the default). The two are mutually
+ * exclusive at the user-facing layer (the SRF entry rejects
+ * specifying both).
+ */
+ Bitmapset *included_kinds;
+ Bitmapset *excluded_kinds;
+
+ /* Derived during setup */
+ Oid base_namespace;
+ char *qualname;
+ char *schema_prefix; /* NULL when schema_qualified */
+ int save_nestlevel; /* >= 0 if we narrowed search_path */
+ LocalNotNullEntry *nn_entries;
+ List *skip_notnull_oids;
+
+ /* Mutable working state */
+ StringInfoData buf;
+ List *statements;
+} TableDdlContext;
+
+static List *pg_get_table_ddl_internal(TableDdlContext *ctx);
+static bool is_kind_included(const TableDdlContext *ctx, TableDdlKind kind);
+static void append_column_defs(StringInfo buf, Relation rel, bool pretty,
+ bool include_check,
+ bool schema_qualified,
+ LocalNotNullEntry * nn_entries);
+static void append_typed_column_overrides(StringInfo buf, Relation rel,
+ bool pretty, bool include_check,
+ LocalNotNullEntry * nn_entries);
+static void append_inline_check_constraints(StringInfo buf, Relation rel,
+ bool pretty, bool *first);
+static char *find_attrdef_text(Relation rel, AttrNumber attnum,
+ List **dpcontext);
+static char *lookup_relname_for_emit(Oid relid, bool schema_qualified,
+ Oid base_namespace);
+static char *strip_schema_prefix(const char *stmt, const char *prefix);
+static LocalNotNullEntry * collect_local_not_null(Relation rel,
+ List **skip_oids);
+
+static void append_stmt(TableDdlContext * ctx);
+static void emit_create_table_stmt(TableDdlContext * ctx);
+static void emit_owner_stmt(TableDdlContext * ctx);
+static void emit_child_default_overrides(TableDdlContext * ctx);
+static void emit_attoptions(TableDdlContext * ctx);
+static void emit_indexes(TableDdlContext * ctx);
+static void emit_local_constraints(TableDdlContext * ctx);
+static void emit_rules(TableDdlContext * ctx);
+static void emit_statistics(TableDdlContext * ctx);
+static void emit_replica_identity(TableDdlContext * ctx);
+static void emit_rls_toggles(TableDdlContext * ctx);
+static void emit_partition_children(TableDdlContext * ctx);
+
/*
* parse_ddl_options
@@ -227,6 +376,102 @@ parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
}
}
+/*
+ * parse_kind_list
+ * Parse a comma-separated list of object-class kind names into a
+ * Bitmapset of TableDdlKind values.
+ *
+ * Whitespace around each entry is trimmed. Empty entries (e.g. "a,,b" or a
+ * trailing comma) are rejected. Unknown kind names raise an error citing
+ * the supplied option name so the message points at the offending option.
+ * Returns NULL only when the list is structurally empty; callers should
+ * treat an all-whitespace list as an error rather than equivalent to NULL.
+ */
+static Bitmapset *
+parse_kind_list(const char *optname, const char *list)
+{
+ Bitmapset *result = NULL;
+ const char *p = list;
+
+ while (*p)
+ {
+ const char *start;
+ const char *end;
+ char *token;
+ bool found = false;
+
+ /* Skip leading whitespace. */
+ while (*p == ' ' || *p == '\t')
+ p++;
+
+ if (*p == ',' || *p == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("empty entry in option \"%s\"", optname)));
+
+ start = p;
+ while (*p && *p != ',' && *p != ' ' && *p != '\t')
+ p++;
+ end = p;
+
+ /* Skip trailing whitespace. */
+ while (*p == ' ' || *p == '\t')
+ p++;
+
+ if (*p != '\0' && *p != ',')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid character in option \"%s\"", optname)));
+
+ if (*p == ',')
+ p++;
+
+ token = pnstrdup(start, end - start);
+
+ for (size_t i = 0; i < lengthof(table_ddl_kind_names); i++)
+ {
+ if (pg_strcasecmp(token, table_ddl_kind_names[i].name) == 0)
+ {
+ result = bms_add_member(result,
+ (int) table_ddl_kind_names[i].kind);
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized kind \"%s\" in option \"%s\"",
+ token, optname)));
+
+ pfree(token);
+ }
+
+ if (result == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("option \"%s\" must specify at least one kind",
+ optname)));
+
+ return result;
+}
+
+/*
+ * is_kind_included
+ * Determine whether DDL for the given object-class kind should be
+ * emitted under the current options.
+ */
+static bool
+is_kind_included(const TableDdlContext *ctx, TableDdlKind kind)
+{
+ if (ctx->included_kinds != NULL)
+ return bms_is_member((int) kind, ctx->included_kinds);
+ if (ctx->excluded_kinds != NULL)
+ return !bms_is_member((int) kind, ctx->excluded_kinds);
+ return true;
+}
+
/*
* Helper to append a formatted string with optional pretty-printing.
*/
@@ -1185,3 +1430,1754 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * lookup_relname_for_emit
+ * Return either the schema-qualified or the bare quoted name of a
+ * relation, depending on the schema_qualified flag.
+ *
+ * When schema_qualified is true the schema-qualified name is always
+ * returned. When false, the bare relname is returned only if the target
+ * relation lives in base_namespace (the namespace of the table whose DDL is
+ * being generated); otherwise the schema-qualified form is returned,
+ * because cross-schema references (for example an inheritance parent or
+ * foreign key target in a different schema) are not safe to strip.
+ *
+ * This replaces the unsafe pattern
+ * quote_qualified_identifier(get_namespace_name(get_rel_namespace(oid)),
+ * get_rel_name(oid))
+ * which dereferences NULL when a concurrent transaction has dropped the
+ * referenced relation (or its schema) between when we cached its OID and
+ * when we ask the syscache for its name. Holding AccessShareLock on a
+ * dependent relation makes this race vanishingly unlikely in practice, but
+ * we still defend against it because the alternative is a SIGSEGV.
+ *
+ * Caller is responsible for pfree()ing the result.
+ */
+static char *
+lookup_relname_for_emit(Oid relid, bool schema_qualified, Oid base_namespace)
+{
+ HeapTuple tp;
+ Form_pg_class reltup;
+ char *nspname;
+ char *result;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tp))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("relation with OID %u does not exist", relid),
+ errdetail("It may have been concurrently dropped.")));
+
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+
+ /* Bare name only when caller asked and target is in the base namespace. */
+ if (!schema_qualified && reltup->relnamespace == base_namespace)
+ {
+ result = pstrdup(quote_identifier(NameStr(reltup->relname)));
+ ReleaseSysCache(tp);
+ return result;
+ }
+
+ nspname = get_namespace_name(reltup->relnamespace);
+ if (nspname == NULL)
+ {
+ Oid nspoid = reltup->relnamespace;
+
+ ReleaseSysCache(tp);
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("schema with OID %u does not exist", nspoid),
+ errdetail("It may have been concurrently dropped.")));
+ }
+
+ result = quote_qualified_identifier(nspname, NameStr(reltup->relname));
+
+ pfree(nspname);
+ ReleaseSysCache(tp);
+
+ return result;
+}
+
+/*
+ * strip_schema_prefix
+ * Return a palloc'd copy of stmt with every occurrence of prefix
+ * removed, except occurrences that fall inside a single-quoted SQL
+ * string literal or a double-quoted SQL identifier.
+ *
+ * Used to strip the qualified-schema prefix (for example "old_schema.")
+ * from DDL emitted by the always-qualified ruleutils helpers
+ * (pg_get_constraintdef_command, pg_get_indexdef_string,
+ * pg_get_ruledef, pg_get_statisticsobjdef_string) when the caller has
+ * requested unqualified output via schema_qualified=false. A plain
+ * substring strip would corrupt string literals whose contents happen
+ * to contain "<schema>." (for example a CHECK comparing against
+ * 'myschema.secret') or double-quoted column/identifier names whose
+ * contents happen to contain "<schema>." (for example a column named
+ * "s.weird" when stripping the prefix "s."), so we track quoting state
+ * and skip matches that occur while we are inside either kind of
+ * quoted token. Escaped quotes ('' inside a string, "" inside an
+ * identifier) are handled by keeping the corresponding flag set across
+ * them.
+ */
+static char *
+strip_schema_prefix(const char *stmt, const char *prefix)
+{
+ StringInfoData buf;
+ size_t plen = strlen(prefix);
+ const char *p = stmt;
+ bool in_string = false;
+ bool in_ident = false;
+
+ initStringInfo(&buf);
+ while (*p)
+ {
+ /*
+ * Try the prefix match BEFORE the quote-state toggles. The prefix
+ * itself may begin with a double quote when the base schema name
+ * requires quoting (e.g. "My-Schema".), and at that leading " we must
+ * strip the whole prefix rather than treat it as the opening of a
+ * regular quoted identifier. The check is gated on !in_ident &&
+ * !in_string, so once we have toggled into a quoted token we never
+ * strip a coincidental substring inside it.
+ */
+ if (!in_string && !in_ident && strncmp(p, prefix, plen) == 0)
+ {
+ p += plen;
+ continue;
+ }
+ if (*p == '\'' && !in_ident)
+ {
+ /* SQL '' escape: emit both quotes and stay inside the string. */
+ if (in_string && p[1] == '\'')
+ {
+ appendStringInfoChar(&buf, '\'');
+ appendStringInfoChar(&buf, '\'');
+ p += 2;
+ continue;
+ }
+ in_string = !in_string;
+ appendStringInfoChar(&buf, *p);
+ p++;
+ continue;
+ }
+ if (*p == '"' && !in_string)
+ {
+ /* SQL "" escape: emit both quotes and stay inside the ident. */
+ if (in_ident && p[1] == '"')
+ {
+ appendStringInfoChar(&buf, '"');
+ appendStringInfoChar(&buf, '"');
+ p += 2;
+ continue;
+ }
+ in_ident = !in_ident;
+ appendStringInfoChar(&buf, *p);
+ p++;
+ continue;
+ }
+ appendStringInfoChar(&buf, *p);
+ p++;
+ }
+ return buf.data;
+}
+
+/*
+ * collect_local_not_null
+ * Scan pg_constraint once for locally-declared NOT NULL constraints
+ * on rel, returning a palloc'd array indexed by attnum (1..natts).
+ *
+ * Entries with conoid==InvalidOid mean "no local NOT NULL row for this
+ * column". When the constraint name does not match the auto-generated
+ * pattern "<tablename>_<columnname>_not_null", the constraint OID is
+ * appended to *skip_oids so the post-CREATE constraint loop can avoid
+ * re-emitting it as ALTER TABLE ... ADD CONSTRAINT - the column-emit
+ * pass will produce it inline as "CONSTRAINT name NOT NULL" instead.
+ */
+static LocalNotNullEntry *
+collect_local_not_null(Relation rel, List **skip_oids)
+{
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ int natts = tupdesc->natts;
+ LocalNotNullEntry *entries;
+ const char *relname = RelationGetRelationName(rel);
+ Relation conRel;
+ SysScanDesc conScan;
+ ScanKeyData conKey;
+ HeapTuple conTup;
+
+ entries = (LocalNotNullEntry *) palloc0(sizeof(LocalNotNullEntry) *
+ (natts + 1));
+
+ conRel = table_open(ConstraintRelationId, AccessShareLock);
+ ScanKeyInit(&conKey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ conScan = systable_beginscan(conRel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &conKey);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(conScan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+ Datum conkeyDat;
+ bool conkeyNull;
+ ArrayType *conkeyArr;
+ int16 *conkeyVals;
+ int attnum;
+ Form_pg_attribute att;
+ char autoname[NAMEDATALEN];
+
+ if (con->contype != CONSTRAINT_NOTNULL)
+ continue;
+ if (!con->conislocal)
+ continue;
+
+ conkeyDat = heap_getattr(conTup, Anum_pg_constraint_conkey,
+ RelationGetDescr(conRel), &conkeyNull);
+ if (conkeyNull)
+ continue;
+ conkeyArr = DatumGetArrayTypeP(conkeyDat);
+
+ /*
+ * Defend against a malformed conkey: a NOT NULL constraint is always
+ * a single-column 1-D int2 array, but a corrupted catalog or a future
+ * patch that stores wider conkeys mustn't trip us into reading past
+ * the array header.
+ */
+ if (ARR_NDIM(conkeyArr) != 1 ||
+ ARR_DIMS(conkeyArr)[0] < 1 ||
+ ARR_HASNULL(conkeyArr) ||
+ ARR_ELEMTYPE(conkeyArr) != INT2OID)
+ continue;
+
+ conkeyVals = (int16 *) ARR_DATA_PTR(conkeyArr);
+ attnum = conkeyVals[0];
+ if (attnum < 1 || attnum > natts)
+ continue;
+
+ att = TupleDescAttr(tupdesc, attnum - 1);
+ snprintf(autoname, sizeof(autoname), "%s_%s_not_null",
+ relname, NameStr(att->attname));
+
+ entries[attnum].conoid = con->oid;
+ entries[attnum].name = pstrdup(NameStr(con->conname));
+ entries[attnum].is_auto = (strcmp(NameStr(con->conname), autoname) == 0);
+ entries[attnum].no_inherit = con->connoinherit;
+
+ /*
+ * Inline emission of NOT NULL only happens for columns that the
+ * column list actually emits, i.e. attislocal columns. For those
+ * the inline pass already materializes the constraint (either as
+ * "CONSTRAINT name NOT NULL" for user-named, or as a bare
+ * "NOT NULL" that PG re-creates under the auto-name pattern), so
+ * the post-CREATE constraint loop must not emit a second
+ * ALTER TABLE ... ADD CONSTRAINT for the same column - PG only
+ * allows one NOT NULL constraint per column and rejects the
+ * second with a name mismatch whenever the saved auto-name no
+ * longer matches the current table name (e.g. after a rename, or
+ * when CREATE TABLE uniquifies the auto-name to dodge a sequence
+ * collision). For a locally-declared NOT NULL sitting on an
+ * inherited (non-local) column the inline path never fires, so
+ * leave the OID out of skip_oids and let the post-CREATE loop
+ * emit ALTER TABLE ... ADD CONSTRAINT.
+ */
+ if (att->attislocal && skip_oids != NULL)
+ *skip_oids = lappend_oid(*skip_oids, con->oid);
+ }
+ systable_endscan(conScan);
+ table_close(conRel, AccessShareLock);
+
+ return entries;
+}
+
+/*
+ * find_attrdef_text
+ * Return the deparsed DEFAULT/GENERATED expression for attnum on rel,
+ * or NULL if no entry exists in TupleConstr->defval.
+ *
+ * The caller passes a List ** so that the deparse context is built lazily
+ * and reused across calls (deparse_context_for is not cheap). Returned
+ * string is palloc'd in the current memory context; caller pfree's it.
+ */
+static char *
+find_attrdef_text(Relation rel, AttrNumber attnum, List **dpcontext)
+{
+ TupleConstr *constr = RelationGetDescr(rel)->constr;
+
+ if (constr == NULL)
+ return NULL;
+
+ for (int j = 0; j < constr->num_defval; j++)
+ {
+ if (constr->defval[j].adnum != attnum)
+ continue;
+
+ if (*dpcontext == NIL)
+ *dpcontext = deparse_context_for(RelationGetRelationName(rel),
+ RelationGetRelid(rel));
+
+ return deparse_expression(stringToNode(constr->defval[j].adbin),
+ *dpcontext, false, false);
+ }
+ return NULL;
+}
+
+/*
+ * append_inline_check_constraints
+ * Emit each locally-declared CHECK constraint on rel as
+ * "CONSTRAINT name <pg_get_constraintdef>", separated by ',' from any
+ * previously-emitted column or constraint.
+ *
+ * *first tracks whether anything has been emitted on this list yet, so the
+ * caller can chain column emission and constraint emission through the same
+ * buffer. Inherited CHECK constraints (!conislocal) come from the parent's
+ * DDL and aren't repeated here.
+ */
+static void
+append_inline_check_constraints(StringInfo buf, Relation rel, bool pretty,
+ bool *first)
+{
+ Relation conRel;
+ SysScanDesc conScan;
+ ScanKeyData conKey;
+ HeapTuple conTup;
+
+ conRel = table_open(ConstraintRelationId, AccessShareLock);
+ ScanKeyInit(&conKey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ conScan = systable_beginscan(conRel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &conKey);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(conScan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+ Datum defDatum;
+ char *defbody;
+
+ if (con->contype != CONSTRAINT_CHECK)
+ continue;
+ if (!con->conislocal)
+ continue;
+
+ if (!*first)
+ appendStringInfoChar(buf, ',');
+ if (pretty)
+ appendStringInfoString(buf, "\n ");
+ else if (!*first)
+ appendStringInfoChar(buf, ' ');
+ *first = false;
+
+ defDatum = OidFunctionCall1(F_PG_GET_CONSTRAINTDEF_OID,
+ ObjectIdGetDatum(con->oid));
+ defbody = TextDatumGetCString(defDatum);
+ appendStringInfo(buf, "CONSTRAINT %s %s",
+ quote_identifier(NameStr(con->conname)),
+ defbody);
+ pfree(defbody);
+ }
+ systable_endscan(conScan);
+ table_close(conRel, AccessShareLock);
+}
+
+/*
+ * append_column_defs
+ * Append the comma-separated column definition list for a table.
+ *
+ * Emits each non-dropped, locally-declared column as
+ * name type [COLLATE x] [STORAGE s] [COMPRESSION c]
+ * [GENERATED ... | DEFAULT e] [NOT NULL]
+ * followed by any locally-declared inline CHECK constraints. Optional
+ * clauses are omitted when their value matches what the system would
+ * reapply on round-trip (e.g. type-default COLLATE, type-default STORAGE).
+ */
+static void
+append_column_defs(StringInfo buf, Relation rel, bool pretty,
+ bool include_check,
+ bool schema_qualified,
+ LocalNotNullEntry * nn_entries)
+{
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ Oid base_namespace = RelationGetNamespace(rel);
+ List *dpcontext = NIL;
+ bool first = true;
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ char *typstr;
+
+ if (att->attisdropped)
+ continue;
+
+ /*
+ * Columns inherited from a parent are emitted by the INHERITS clause
+ * (once implemented), not the column list, unless the child
+ * redeclared them locally (attislocal=true).
+ */
+ if (!att->attislocal)
+ continue;
+
+ if (!first)
+ appendStringInfoChar(buf, ',');
+ if (pretty)
+ appendStringInfoString(buf, "\n ");
+ else if (!first)
+ appendStringInfoChar(buf, ' ');
+ first = false;
+
+ appendStringInfoString(buf, quote_identifier(NameStr(att->attname)));
+ appendStringInfoChar(buf, ' ');
+
+ typstr = format_type_with_typemod(att->atttypid, att->atttypmod);
+ appendStringInfoString(buf, typstr);
+ pfree(typstr);
+
+ /* COLLATE clause, only if it differs from the type's default. */
+ if (OidIsValid(att->attcollation) &&
+ att->attcollation != get_typcollation(att->atttypid))
+ appendStringInfo(buf, " COLLATE %s",
+ generate_collation_name(att->attcollation));
+
+ /* STORAGE clause, only if it differs from the type's default. */
+ if (att->attstorage != get_typstorage(att->atttypid))
+ appendStringInfo(buf, " STORAGE %s", storage_name(att->attstorage));
+
+ /* COMPRESSION clause, only if explicitly set on the column. */
+ if (CompressionMethodIsValid(att->attcompression))
+ appendStringInfo(buf, " COMPRESSION %s",
+ GetCompressionMethodName(att->attcompression));
+
+ /*
+ * Look up the default/generated expression text up front; generated
+ * columns have atthasdef=true with an entry in pg_attrdef just like
+ * regular defaults.
+ */
+ {
+ char *defexpr = NULL;
+
+ if (att->atthasdef)
+ defexpr = find_attrdef_text(rel, att->attnum, &dpcontext);
+
+ /* GENERATED / IDENTITY / DEFAULT are mutually exclusive. */
+ if (att->attgenerated == ATTRIBUTE_GENERATED_STORED && defexpr)
+ appendStringInfo(buf, " GENERATED ALWAYS AS (%s) STORED", defexpr);
+ else if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && defexpr)
+ appendStringInfo(buf, " GENERATED ALWAYS AS (%s) VIRTUAL", defexpr);
+ else if (att->attidentity == ATTRIBUTE_IDENTITY_ALWAYS ||
+ att->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT)
+ {
+ const char *idkind =
+ (att->attidentity == ATTRIBUTE_IDENTITY_ALWAYS)
+ ? "ALWAYS" : "BY DEFAULT";
+ Oid seqid = getIdentitySequence(rel, att->attnum, true);
+
+ appendStringInfo(buf, " GENERATED %s AS IDENTITY", idkind);
+
+ /*
+ * Emit only the sequence options that differ from their
+ * defaults - mirroring pg_get_database_ddl's pattern of
+ * omitting values that the system would reapply on its own.
+ */
+ if (OidIsValid(seqid))
+ {
+ HeapTuple seqTup = SearchSysCache1(SEQRELID,
+ ObjectIdGetDatum(seqid));
+
+ if (HeapTupleIsValid(seqTup))
+ {
+ Form_pg_sequence seq = (Form_pg_sequence) GETSTRUCT(seqTup);
+ StringInfoData opts;
+ bool first_opt = true;
+ int64 def_min,
+ def_max,
+ def_start;
+ int64 typ_min,
+ typ_max;
+
+ /*
+ * Per-type bounds for the sequence's underlying
+ * integer type. Defaults to int8 if the column type
+ * is something else (shouldn't happen for IDENTITY,
+ * but be defensive).
+ */
+ switch (att->atttypid)
+ {
+ case INT2OID:
+ typ_min = PG_INT16_MIN;
+ typ_max = PG_INT16_MAX;
+ break;
+ case INT4OID:
+ typ_min = PG_INT32_MIN;
+ typ_max = PG_INT32_MAX;
+ break;
+ default:
+ typ_min = PG_INT64_MIN;
+ typ_max = PG_INT64_MAX;
+ break;
+ }
+
+ if (seq->seqincrement > 0)
+ {
+ def_min = 1;
+ def_max = typ_max;
+ def_start = def_min;
+ }
+ else
+ {
+ def_min = typ_min;
+ def_max = -1;
+ def_start = def_max;
+ }
+
+ initStringInfo(&opts);
+
+ /*
+ * SEQUENCE NAME - omit when it matches the implicit
+ * "<tablename>_<columnname>_seq" pattern in the same
+ * schema, since CREATE TABLE will regenerate that
+ * exact name. The sequence is an INTERNAL dependency
+ * of the column, so the lock we hold on the table
+ * also pins it, but the lookup helper still defends
+ * against a missing pg_class row.
+ */
+ {
+ HeapTuple seqClassTup;
+ Form_pg_class seqClass;
+ char autoname[NAMEDATALEN];
+
+ seqClassTup = SearchSysCache1(RELOID,
+ ObjectIdGetDatum(seqid));
+ if (!HeapTupleIsValid(seqClassTup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("identity sequence with OID %u does not exist",
+ seqid),
+ errdetail("It may have been concurrently dropped.")));
+ seqClass = (Form_pg_class) GETSTRUCT(seqClassTup);
+
+ snprintf(autoname, sizeof(autoname), "%s_%s_seq",
+ RelationGetRelationName(rel),
+ NameStr(att->attname));
+ if (seqClass->relnamespace != RelationGetNamespace(rel) ||
+ strcmp(NameStr(seqClass->relname), autoname) != 0)
+ {
+ char *seqQual =
+ lookup_relname_for_emit(seqid,
+ schema_qualified,
+ base_namespace);
+
+ appendStringInfo(&opts, "%sSEQUENCE NAME %s",
+ first_opt ? "" : " ", seqQual);
+ first_opt = false;
+ pfree(seqQual);
+ }
+ ReleaseSysCache(seqClassTup);
+ }
+
+ if (seq->seqstart != def_start)
+ {
+ appendStringInfo(&opts, "%sSTART WITH " INT64_FORMAT,
+ first_opt ? "" : " ", seq->seqstart);
+ first_opt = false;
+ }
+ if (seq->seqincrement != 1)
+ {
+ appendStringInfo(&opts, "%sINCREMENT BY " INT64_FORMAT,
+ first_opt ? "" : " ", seq->seqincrement);
+ first_opt = false;
+ }
+ if (seq->seqmin != def_min)
+ {
+ appendStringInfo(&opts, "%sMINVALUE " INT64_FORMAT,
+ first_opt ? "" : " ", seq->seqmin);
+ first_opt = false;
+ }
+ if (seq->seqmax != def_max)
+ {
+ appendStringInfo(&opts, "%sMAXVALUE " INT64_FORMAT,
+ first_opt ? "" : " ", seq->seqmax);
+ first_opt = false;
+ }
+ if (seq->seqcache != 1)
+ {
+ appendStringInfo(&opts, "%sCACHE " INT64_FORMAT,
+ first_opt ? "" : " ", seq->seqcache);
+ first_opt = false;
+ }
+ if (seq->seqcycle)
+ {
+ appendStringInfo(&opts, "%sCYCLE", first_opt ? "" : " ");
+ first_opt = false;
+ }
+
+ if (!first_opt)
+ appendStringInfo(buf, " (%s)", opts.data);
+
+ pfree(opts.data);
+ ReleaseSysCache(seqTup);
+ }
+ }
+ }
+ else if (defexpr)
+ appendStringInfo(buf, " DEFAULT %s", defexpr);
+
+ if (defexpr)
+ pfree(defexpr);
+ }
+
+ if (att->attnotnull)
+ {
+ LocalNotNullEntry *nn = &nn_entries[att->attnum];
+
+ if (nn->name != NULL && !nn->is_auto)
+ appendStringInfo(buf, " CONSTRAINT %s NOT NULL",
+ quote_identifier(nn->name));
+ else
+ appendStringInfoString(buf, " NOT NULL");
+ if (nn->name != NULL && nn->no_inherit)
+ appendStringInfoString(buf, " NO INHERIT");
+ }
+ }
+
+ /*
+ * Table-level CHECK constraints - emitted inline in the CREATE TABLE body
+ * so they appear alongside the columns (the pg_dump shape). The
+ * constraint loop later in pg_get_table_ddl_internal skips CHECK
+ * constraints to avoid double-emission.
+ */
+ if (include_check)
+ append_inline_check_constraints(buf, rel, pretty, &first);
+}
+
+/*
+ * append_typed_column_overrides
+ * For a typed table (CREATE TABLE ... OF type_name), append the
+ * optional "(col WITH OPTIONS ..., ...)" list carrying locally
+ * applied per-column overrides - DEFAULT, NOT NULL, and any locally
+ * declared CHECK constraints.
+ *
+ * Columns whose type is fully dictated by reloftype emit nothing. The
+ * parenthesised list is suppressed entirely when no column needs an
+ * override and there are no locally-declared CHECK constraints, matching
+ * the canonical "CREATE TABLE x OF t;" shape.
+ */
+static void
+append_typed_column_overrides(StringInfo buf, Relation rel, bool pretty,
+ bool include_check,
+ LocalNotNullEntry * nn_entries)
+{
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ List *dpcontext = NIL;
+ StringInfoData inner;
+ bool first = true;
+
+ initStringInfo(&inner);
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ char *defexpr = NULL;
+ bool has_default;
+ bool has_notnull;
+
+ if (att->attisdropped)
+ continue;
+
+ if (att->atthasdef)
+ defexpr = find_attrdef_text(rel, att->attnum, &dpcontext);
+
+ has_default = (defexpr != NULL);
+ has_notnull = att->attnotnull;
+
+ if (!has_default && !has_notnull)
+ {
+ if (defexpr)
+ pfree(defexpr);
+ continue;
+ }
+
+ if (!first)
+ appendStringInfoChar(&inner, ',');
+ if (pretty)
+ appendStringInfoString(&inner, "\n ");
+ else if (!first)
+ appendStringInfoChar(&inner, ' ');
+ first = false;
+
+ appendStringInfo(&inner, "%s WITH OPTIONS",
+ quote_identifier(NameStr(att->attname)));
+ if (has_default)
+ appendStringInfo(&inner, " DEFAULT %s", defexpr);
+ if (has_notnull)
+ {
+ LocalNotNullEntry *nn = &nn_entries[att->attnum];
+
+ if (nn->name != NULL && !nn->is_auto)
+ appendStringInfo(&inner, " CONSTRAINT %s NOT NULL",
+ quote_identifier(nn->name));
+ else
+ appendStringInfoString(&inner, " NOT NULL");
+ if (nn->name != NULL && nn->no_inherit)
+ appendStringInfoString(&inner, " NO INHERIT");
+ }
+
+ if (defexpr)
+ pfree(defexpr);
+ }
+
+ /*
+ * Locally-declared CHECK constraints on a typed table belong in the
+ * column-list parentheses, same as for an untyped table. The out-of-line
+ * constraint loop later still skips CHECKs.
+ */
+ if (include_check)
+ append_inline_check_constraints(&inner, rel, pretty, &first);
+
+ if (!first)
+ {
+ appendStringInfoString(buf, " (");
+ appendStringInfoString(buf, inner.data);
+ if (pretty)
+ appendStringInfoString(buf, "\n)");
+ else
+ appendStringInfoChar(buf, ')');
+ }
+ pfree(inner.data);
+}
+
+/*
+ * append_stmt / append_stripped_stmt
+ * Push ctx->buf onto ctx->statements.
+ *
+ * append_stmt is used for emissions where the table reference is built
+ * from ctx->qualname (already partial-qualified per schema_qualified).
+ * append_stripped_stmt is used after invoking always-qualified ruleutils
+ * helpers (pg_get_constraintdef_command, pg_get_indexdef_string,
+ * pg_get_ruledef, pg_get_statisticsobjdef_string) - when
+ * schema_qualified is false, the base-schema prefix is stripped from
+ * their output so the emitted DDL is replay-relocatable.
+ */
+static void
+append_stmt(TableDdlContext * ctx)
+{
+ ctx->statements = lappend(ctx->statements, pstrdup(ctx->buf.data));
+}
+
+static void
+append_stripped_stmt(TableDdlContext * ctx)
+{
+ if (ctx->schema_prefix != NULL)
+ ctx->statements = lappend(ctx->statements,
+ strip_schema_prefix(ctx->buf.data,
+ ctx->schema_prefix));
+ else
+ ctx->statements = lappend(ctx->statements, pstrdup(ctx->buf.data));
+}
+
+/*
+ * emit_create_table_stmt
+ * Build the leading CREATE TABLE statement, including persistence
+ * (TEMPORARY / UNLOGGED), body (column list / OF type_name /
+ * PARTITION OF parent), INHERITS, PARTITION BY, USING method,
+ * WITH (reloptions), TABLESPACE, and ON COMMIT.
+ */
+static void
+emit_create_table_stmt(TableDdlContext * ctx)
+{
+ Relation rel = ctx->rel;
+ char relpersistence = rel->rd_rel->relpersistence;
+ char relkind = rel->rd_rel->relkind;
+ bool is_typed = OidIsValid(rel->rd_rel->reloftype);
+ HeapTuple classtup;
+ Datum reloptDatum;
+ bool reloptIsnull;
+
+ classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(ctx->relid));
+ if (!HeapTupleIsValid(classtup))
+ elog(ERROR, "cache lookup failed for relation %u", ctx->relid);
+
+ reloptDatum = SysCacheGetAttr(RELOID, classtup,
+ Anum_pg_class_reloptions, &reloptIsnull);
+
+ resetStringInfo(&ctx->buf);
+ appendStringInfoString(&ctx->buf, "CREATE ");
+ if (relpersistence == RELPERSISTENCE_TEMP)
+ appendStringInfoString(&ctx->buf, "TEMPORARY ");
+ else if (relpersistence == RELPERSISTENCE_UNLOGGED)
+ appendStringInfoString(&ctx->buf, "UNLOGGED ");
+ appendStringInfo(&ctx->buf, "TABLE %s", ctx->qualname);
+
+ if (rel->rd_rel->relispartition)
+ {
+ Oid parentOid = get_partition_parent(ctx->relid, true);
+ char *parentQual = lookup_relname_for_emit(parentOid,
+ ctx->schema_qualified,
+ ctx->base_namespace);
+ char *parentRelname = get_rel_name(parentOid);
+ Datum boundDatum;
+ bool boundIsnull;
+ char *forValues = NULL;
+ char *boundStr = NULL;
+
+ if (parentRelname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("partition parent with OID %u does not exist",
+ parentOid),
+ errdetail("It may have been concurrently dropped.")));
+
+ boundDatum = SysCacheGetAttr(RELOID, classtup,
+ Anum_pg_class_relpartbound, &boundIsnull);
+ if (!boundIsnull)
+ {
+ Node *boundNode;
+ List *dpcontext;
+
+ boundStr = TextDatumGetCString(boundDatum);
+ boundNode = stringToNode(boundStr);
+ dpcontext = deparse_context_for(parentRelname, parentOid);
+ forValues = deparse_expression(boundNode, dpcontext, false, false);
+ }
+
+ appendStringInfo(&ctx->buf, " PARTITION OF %s %s",
+ parentQual, forValues ? forValues : "DEFAULT");
+ if (forValues)
+ pfree(forValues);
+ if (boundStr)
+ pfree(boundStr);
+ pfree(parentQual);
+ pfree(parentRelname);
+ }
+ else if (is_typed)
+ {
+ char *typname = format_type_be_qualified(rel->rd_rel->reloftype);
+
+ appendStringInfo(&ctx->buf, " OF %s", typname);
+ pfree(typname);
+
+ append_typed_column_overrides(&ctx->buf, rel, ctx->pretty,
+ is_kind_included(ctx, TABLE_DDL_KIND_CHECK),
+ ctx->nn_entries);
+ }
+ else
+ {
+ List *parents;
+ ListCell *lc;
+ bool first;
+
+ appendStringInfoString(&ctx->buf, " (");
+
+ append_column_defs(&ctx->buf, rel, ctx->pretty,
+ is_kind_included(ctx, TABLE_DDL_KIND_CHECK),
+ ctx->schema_qualified, ctx->nn_entries);
+
+ if (ctx->pretty)
+ appendStringInfoString(&ctx->buf, "\n)");
+ else
+ appendStringInfoChar(&ctx->buf, ')');
+
+ parents = find_inheritance_parents(ctx->relid, NoLock);
+ if (parents != NIL)
+ {
+ appendStringInfoString(&ctx->buf, " INHERITS (");
+ first = true;
+ foreach(lc, parents)
+ {
+ Oid poid = lfirst_oid(lc);
+ char *pname = lookup_relname_for_emit(poid,
+ ctx->schema_qualified,
+ ctx->base_namespace);
+
+ if (!first)
+ appendStringInfoString(&ctx->buf, ", ");
+ first = false;
+ appendStringInfoString(&ctx->buf, pname);
+ pfree(pname);
+ }
+ appendStringInfoChar(&ctx->buf, ')');
+ list_free(parents);
+ }
+ }
+
+ if (relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ Datum partkeyDatum;
+ char *partkey;
+
+ partkeyDatum = OidFunctionCall1(F_PG_GET_PARTKEYDEF,
+ ObjectIdGetDatum(ctx->relid));
+ partkey = TextDatumGetCString(partkeyDatum);
+ appendStringInfo(&ctx->buf, " PARTITION BY %s", partkey);
+ pfree(partkey);
+ }
+
+ if (OidIsValid(rel->rd_rel->relam) &&
+ rel->rd_rel->relam != HEAP_TABLE_AM_OID)
+ {
+ char *amname = get_am_name(rel->rd_rel->relam);
+
+ if (amname != NULL)
+ {
+ appendStringInfo(&ctx->buf, " USING %s",
+ quote_identifier(amname));
+ pfree(amname);
+ }
+ }
+
+ if (!reloptIsnull)
+ {
+ appendStringInfoString(&ctx->buf, " WITH (");
+ get_reloptions(&ctx->buf, reloptDatum);
+ appendStringInfoChar(&ctx->buf, ')');
+ }
+
+ ReleaseSysCache(classtup);
+
+ if (!ctx->no_tablespace && OidIsValid(rel->rd_rel->reltablespace))
+ {
+ char *tsname = get_tablespace_name(rel->rd_rel->reltablespace);
+
+ if (tsname != NULL)
+ {
+ appendStringInfo(&ctx->buf, " TABLESPACE %s",
+ quote_identifier(tsname));
+ pfree(tsname);
+ }
+ }
+
+ if (relpersistence == RELPERSISTENCE_TEMP)
+ {
+ OnCommitAction oc = get_on_commit_action(ctx->relid);
+
+ if (oc == ONCOMMIT_DELETE_ROWS)
+ appendStringInfoString(&ctx->buf, " ON COMMIT DELETE ROWS");
+ else if (oc == ONCOMMIT_DROP)
+ appendStringInfoString(&ctx->buf, " ON COMMIT DROP");
+ }
+
+ appendStringInfoChar(&ctx->buf, ';');
+ append_stmt(ctx);
+}
+
+/*
+ * emit_owner_stmt
+ * ALTER TABLE qualname OWNER TO role.
+ */
+static void
+emit_owner_stmt(TableDdlContext * ctx)
+{
+ char *owner;
+
+ if (ctx->no_owner)
+ return;
+
+ owner = GetUserNameFromId(ctx->rel->rd_rel->relowner, false);
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf, "ALTER TABLE %s OWNER TO %s;",
+ ctx->qualname, quote_identifier(owner));
+ append_stmt(ctx);
+ pfree(owner);
+}
+
+/*
+ * emit_child_default_overrides
+ * ALTER TABLE qualname ALTER COLUMN col SET DEFAULT expr - one per
+ * inherited (attislocal=false) non-generated column carrying a
+ * locally-set default. Generated columns are skipped: their
+ * expression is inherited automatically and SET DEFAULT would
+ * fail at replay.
+ */
+static void
+emit_child_default_overrides(TableDdlContext * ctx)
+{
+ TupleDesc tupdesc = RelationGetDescr(ctx->rel);
+ List *dpcontext = NIL;
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ char *defstr;
+
+ if (att->attisdropped || att->attislocal || !att->atthasdef)
+ continue;
+ if (att->attgenerated != '\0')
+ continue;
+
+ defstr = find_attrdef_text(ctx->rel, att->attnum, &dpcontext);
+ if (defstr == NULL)
+ continue;
+
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;",
+ ctx->qualname,
+ quote_identifier(NameStr(att->attname)),
+ defstr);
+ append_stmt(ctx);
+ pfree(defstr);
+ }
+}
+
+/*
+ * emit_attoptions
+ * ALTER TABLE qualname ALTER COLUMN col SET (...) - one per column
+ * with non-null pg_attribute.attoptions. The inline form of these
+ * options isn't available in CREATE TABLE, so they come out here.
+ */
+static void
+emit_attoptions(TableDdlContext * ctx)
+{
+ TupleDesc tupdesc = RelationGetDescr(ctx->rel);
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ HeapTuple attTup;
+ Datum optDatum;
+ bool optIsnull;
+
+ if (att->attisdropped)
+ continue;
+
+ attTup = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(ctx->relid),
+ Int16GetDatum(att->attnum));
+ if (!HeapTupleIsValid(attTup))
+ continue;
+
+ optDatum = SysCacheGetAttr(ATTNUM, attTup,
+ Anum_pg_attribute_attoptions, &optIsnull);
+ if (!optIsnull)
+ {
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s ALTER COLUMN %s SET (",
+ ctx->qualname,
+ quote_identifier(NameStr(att->attname)));
+ get_reloptions(&ctx->buf, optDatum);
+ appendStringInfoString(&ctx->buf, ");");
+ append_stmt(ctx);
+ }
+ ReleaseSysCache(attTup);
+ }
+}
+
+/*
+ * emit_indexes
+ * CREATE INDEX per non-constraint-backed index on the relation.
+ * Indexes that back PK / UNIQUE / EXCLUDE constraints are emitted
+ * out-of-line by emit_local_constraints (the ALTER TABLE ... ADD
+ * CONSTRAINT statement creates the index implicitly).
+ */
+static void
+emit_indexes(TableDdlContext * ctx)
+{
+ List *indexoids;
+ ListCell *lc;
+
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_INDEXES))
+ return;
+
+ indexoids = RelationGetIndexList(ctx->rel);
+ foreach(lc, indexoids)
+ {
+ Oid idxoid = lfirst_oid(lc);
+ char *idxdef;
+
+ if (OidIsValid(get_index_constraint(idxoid)))
+ continue;
+
+ idxdef = pg_get_indexdef_string(idxoid);
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf, "%s;", idxdef);
+ append_stripped_stmt(ctx);
+ pfree(idxdef);
+ }
+ list_free(indexoids);
+}
+
+/*
+ * emit_local_constraints
+ * ALTER TABLE ... ADD CONSTRAINT for each locally-defined constraint
+ * on the relation. Inherited constraints (conislocal=false) come
+ * from the parent's DDL. CHECK constraints are emitted inline for
+ * regular/typed tables (skip here) but out-of-line for partition
+ * children (no column list to live in). Local NOT NULL
+ * constraints on attislocal columns - whether user-named or
+ * matching the auto-name pattern - are emitted inline by the
+ * column-emit helpers and are skipped via skip_notnull_oids,
+ * because PG only allows one NOT NULL per column and would reject
+ * a second ALTER TABLE ... ADD CONSTRAINT. Partition children
+ * have no column list, so their NOT NULLs fall through here.
+ *
+ * Each contype is gated on the matching kind in the include /
+ * exclude vocabulary, so callers can produce e.g. an FK-only pass
+ * (include => 'foreign_keys') or a pub/sub clone that keeps only
+ * the primary key (exclude => 'unique,check,foreign_keys,exclusion').
+ * NOT NULL is intentionally not in the vocabulary - always emitted
+ * so cloned schemas don't silently accept NULLs the source would
+ * have rejected.
+ */
+static void
+emit_local_constraints(TableDdlContext * ctx)
+{
+ Relation conRel;
+ SysScanDesc conScan;
+ ScanKeyData conKey;
+ HeapTuple conTup;
+ bool is_partition = ctx->rel->rd_rel->relispartition;
+
+ conRel = table_open(ConstraintRelationId, AccessShareLock);
+ ScanKeyInit(&conKey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ctx->relid));
+ conScan = systable_beginscan(conRel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &conKey);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(conScan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+ char *condef;
+
+ if (!con->conislocal)
+ continue;
+
+ /*
+ * Each contype is gated on its kind in the include/exclude
+ * vocabulary. CHECK is also skipped for non-partition relations
+ * because append_inline_check_constraints emits those inline in
+ * the CREATE TABLE body; partition children have no column list
+ * to live in, so they come through this loop. NOT NULL is not a
+ * filterable kind - emitted unconditionally to avoid producing
+ * schemas that silently accept NULLs the source would have
+ * rejected.
+ */
+ switch (con->contype)
+ {
+ case CONSTRAINT_PRIMARY:
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_PRIMARY_KEY))
+ continue;
+ break;
+ case CONSTRAINT_UNIQUE:
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_UNIQUE))
+ continue;
+ break;
+ case CONSTRAINT_CHECK:
+ if (!is_partition)
+ continue;
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_CHECK))
+ continue;
+ break;
+ case CONSTRAINT_FOREIGN:
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_FOREIGN_KEYS))
+ continue;
+ break;
+ case CONSTRAINT_EXCLUSION:
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_EXCLUSION))
+ continue;
+ break;
+ case CONSTRAINT_NOTNULL:
+ /*
+ * Out-of-line NOT NULL is conceptually part of the
+ * table definition: gating it on KIND_TABLE means an
+ * include=foreign_keys second pass does not re-emit
+ * NOT NULLs that the first pass already created,
+ * while the no-options default still emits them.
+ */
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_TABLE))
+ continue;
+ if (!is_partition &&
+ list_member_oid(ctx->skip_notnull_oids, con->oid))
+ continue;
+ break;
+ default:
+ /*
+ * Any future contype the vocabulary does not yet cover:
+ * fall through and emit it via pg_get_constraintdef_command,
+ * matching the original loop's "emit unless filtered"
+ * behavior. This is unreachable in current PG (every
+ * contype above is enumerated) but kept defensive against
+ * a new contype being introduced.
+ */
+ break;
+ }
+
+ condef = pg_get_constraintdef_command(con->oid);
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf, "%s;", condef);
+ append_stripped_stmt(ctx);
+ pfree(condef);
+ }
+ systable_endscan(conScan);
+ table_close(conRel, AccessShareLock);
+}
+
+/*
+ * emit_rules
+ * CREATE RULE per cached rewrite rule on the relation.
+ */
+static void
+emit_rules(TableDdlContext * ctx)
+{
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_RULES) ||
+ ctx->rel->rd_rules == NULL)
+ return;
+
+ for (int i = 0; i < ctx->rel->rd_rules->numLocks; i++)
+ {
+ Oid ruleid = ctx->rel->rd_rules->rules[i]->ruleId;
+ Datum ruledef;
+ char *ruledef_str;
+
+ ruledef = OidFunctionCall1(F_PG_GET_RULEDEF_OID,
+ ObjectIdGetDatum(ruleid));
+ ruledef_str = TextDatumGetCString(ruledef);
+ resetStringInfo(&ctx->buf);
+ appendStringInfoString(&ctx->buf, ruledef_str);
+ append_stripped_stmt(ctx);
+ pfree(ruledef_str);
+ }
+}
+
+/*
+ * emit_statistics
+ * CREATE STATISTICS per extended statistics object on the relation.
+ */
+static void
+emit_statistics(TableDdlContext * ctx)
+{
+ Relation statRel;
+ SysScanDesc statScan;
+ ScanKeyData statKey;
+ HeapTuple statTup;
+
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_STATISTICS))
+ return;
+
+ statRel = table_open(StatisticExtRelationId, AccessShareLock);
+ ScanKeyInit(&statKey,
+ Anum_pg_statistic_ext_stxrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ctx->relid));
+ statScan = systable_beginscan(statRel, StatisticExtRelidIndexId,
+ true, NULL, 1, &statKey);
+
+ while (HeapTupleIsValid(statTup = systable_getnext(statScan)))
+ {
+ Form_pg_statistic_ext stat = (Form_pg_statistic_ext) GETSTRUCT(statTup);
+ char *statdef = pg_get_statisticsobjdef_string(stat->oid);
+
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf, "%s;", statdef);
+ append_stripped_stmt(ctx);
+ pfree(statdef);
+ }
+ systable_endscan(statScan);
+ table_close(statRel, AccessShareLock);
+}
+
+/*
+ * emit_replica_identity
+ * ALTER TABLE qualname REPLICA IDENTITY ... - emitted only when the
+ * relreplident differs from the default ('d' = use primary key).
+ */
+static void
+emit_replica_identity(TableDdlContext * ctx)
+{
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_REPLICA_IDENTITY))
+ return;
+ if (ctx->rel->rd_rel->relreplident == REPLICA_IDENTITY_DEFAULT)
+ return;
+
+ resetStringInfo(&ctx->buf);
+ switch (ctx->rel->rd_rel->relreplident)
+ {
+ case REPLICA_IDENTITY_NOTHING:
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s REPLICA IDENTITY NOTHING;",
+ ctx->qualname);
+ append_stmt(ctx);
+ break;
+ case REPLICA_IDENTITY_FULL:
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s REPLICA IDENTITY FULL;",
+ ctx->qualname);
+ append_stmt(ctx);
+ break;
+ case REPLICA_IDENTITY_INDEX:
+ {
+ Oid replidx = RelationGetReplicaIndex(ctx->rel);
+
+ if (OidIsValid(replidx))
+ {
+ char *idxname = get_rel_name(replidx);
+
+ if (idxname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("replica identity index with OID %u does not exist",
+ replidx),
+ errdetail("It may have been concurrently dropped.")));
+
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s REPLICA IDENTITY USING INDEX %s;",
+ ctx->qualname,
+ quote_identifier(idxname));
+ append_stmt(ctx);
+ pfree(idxname);
+ }
+ }
+ break;
+ }
+}
+
+/*
+ * emit_rls_toggles
+ * ALTER TABLE qualname ENABLE / FORCE ROW LEVEL SECURITY.
+ */
+static void
+emit_rls_toggles(TableDdlContext * ctx)
+{
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_RLS))
+ return;
+
+ if (ctx->rel->rd_rel->relrowsecurity)
+ {
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s ENABLE ROW LEVEL SECURITY;",
+ ctx->qualname);
+ append_stmt(ctx);
+ }
+ if (ctx->rel->rd_rel->relforcerowsecurity)
+ {
+ resetStringInfo(&ctx->buf);
+ appendStringInfo(&ctx->buf,
+ "ALTER TABLE %s FORCE ROW LEVEL SECURITY;",
+ ctx->qualname);
+ append_stmt(ctx);
+ }
+}
+
+/*
+ * emit_partition_children
+ * For each direct partition child of a partitioned-table parent,
+ * recursively call pg_get_table_ddl_internal and append the child's
+ * statements. Each child's own DDL handles further levels through
+ * the same recursion.
+ */
+static void
+emit_partition_children(TableDdlContext * ctx)
+{
+ List *children;
+ ListCell *lc;
+
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_PARTITIONS) ||
+ ctx->rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ return;
+
+ children = find_inheritance_children(ctx->relid, AccessShareLock);
+ foreach(lc, children)
+ {
+ Oid childoid = lfirst_oid(lc);
+ TableDdlContext childctx = {0};
+ List *childstmts;
+
+ /*
+ * Each recursive invocation re-derives its own rel, namespace,
+ * qualname, NOT NULL cache, buffer, and statement list inside
+ * pg_get_table_ddl_internal. The user-supplied option fields
+ * carry over verbatim; the included_kinds / excluded_kinds
+ * bitmapsets are shared by pointer (read-only, freed once by the
+ * top-level caller's memory context cleanup).
+ */
+ childctx.relid = childoid;
+ childctx.pretty = ctx->pretty;
+ childctx.no_owner = ctx->no_owner;
+ childctx.no_tablespace = ctx->no_tablespace;
+ childctx.schema_qualified = ctx->schema_qualified;
+ childctx.included_kinds = ctx->included_kinds;
+ childctx.excluded_kinds = ctx->excluded_kinds;
+
+ childstmts = pg_get_table_ddl_internal(&childctx);
+ ctx->statements = list_concat(ctx->statements, childstmts);
+ }
+ list_free(children);
+}
+
+/*
+ * pg_get_table_ddl_internal
+ * Generate DDL statements to recreate a regular or partitioned table.
+ *
+ * The caller initializes *ctx with the user-supplied option fields
+ * (relid, pretty, no_owner, no_tablespace, schema_qualified,
+ * included_kinds, excluded_kinds). This function opens the relation,
+ * validates access, populates the derived fields (rel, qualname,
+ * schema_prefix, nn_entries, ...), runs the emission passes, and
+ * returns the accumulated statement list.
+ *
+ * Each emission helper consults is_kind_included() to decide whether
+ * it should run. The table-proper passes (CREATE TABLE / OWNER /
+ * ALTER COLUMN ... SET DEFAULT / ALTER COLUMN ... SET (...)) are
+ * grouped under KIND_TABLE so the FK-only / sub-object-only workflow
+ * is expressible as a single include or exclude list.
+ *
+ * Trigger and policy emission are scaffolded but currently disabled
+ * (#if 0) - they will become a single helper call once the standalone
+ * pg_get_trigger_ddl / pg_get_policy_ddl helpers land.
+ */
+static List *
+pg_get_table_ddl_internal(TableDdlContext *ctx)
+{
+ Relation rel;
+ char relkind;
+ AclResult aclresult;
+
+ rel = table_open(ctx->relid, AccessShareLock);
+
+ relkind = rel->rd_rel->relkind;
+
+ /*
+ * The initial cut only supports ordinary and partitioned tables. Views,
+ * matviews, foreign tables, sequences, indexes, composite types, and
+ * TOAST tables are out of scope for now.
+ */
+ if (relkind != RELKIND_RELATION && relkind != RELKIND_PARTITIONED_TABLE)
+ {
+ char *relname = pstrdup(RelationGetRelationName(rel));
+
+ table_close(rel, AccessShareLock);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not an ordinary or partitioned table",
+ relname)));
+ }
+
+ /* Caller needs SELECT on the table to read its definition. */
+ aclresult = pg_class_aclcheck(ctx->relid, GetUserId(), ACL_SELECT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_TABLE,
+ RelationGetRelationName(rel));
+
+ /*
+ * Validation: excluding "primary_key" while the table's REPLICA IDENTITY
+ * is set to USING INDEX of the PK would produce DDL that references an
+ * index the same DDL just dropped. Require the user to also exclude
+ * "replica_identity" in that case, or to not exclude the PK.
+ */
+ if (ctx->excluded_kinds != NULL &&
+ bms_is_member((int) TABLE_DDL_KIND_PRIMARY_KEY,
+ ctx->excluded_kinds) &&
+ !bms_is_member((int) TABLE_DDL_KIND_REPLICA_IDENTITY,
+ ctx->excluded_kinds) &&
+ rel->rd_rel->relreplident == REPLICA_IDENTITY_INDEX)
+ {
+ Oid replidx = RelationGetReplicaIndex(rel);
+
+ if (OidIsValid(replidx))
+ {
+ HeapTuple idxTup = SearchSysCache1(INDEXRELID,
+ ObjectIdGetDatum(replidx));
+
+ if (HeapTupleIsValid(idxTup))
+ {
+ bool is_pk = ((Form_pg_index) GETSTRUCT(idxTup))->indisprimary;
+
+ ReleaseSysCache(idxTup);
+
+ if (is_pk)
+ {
+ char *relname = pstrdup(RelationGetRelationName(rel));
+
+ table_close(rel, AccessShareLock);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot exclude \"primary_key\" without also excluding \"replica_identity\" for table \"%s\"",
+ relname),
+ errdetail("The table's REPLICA IDENTITY references the primary key index.")));
+ }
+ }
+ }
+ }
+
+ /*
+ * Populate derived fields now that the relation is open and validated.
+ * The remaining derived fields (schema_prefix, nn_entries,
+ * skip_notnull_oids, buf, statements) start zeroed via the caller's
+ * `TableDdlContext ctx = {0}` initializer; schema_prefix is set
+ * conditionally below, the NOT NULL cache is populated by
+ * collect_local_not_null, the buffer is initStringInfo'd, and each
+ * emit pass appends to statements via lappend.
+ */
+ ctx->rel = rel;
+ ctx->base_namespace = RelationGetNamespace(rel);
+ ctx->save_nestlevel = -1;
+ ctx->qualname = lookup_relname_for_emit(ctx->relid, ctx->schema_qualified,
+ ctx->base_namespace);
+
+ /*
+ * When schema_qualified is false, narrow the active search_path to the
+ * target table's own namespace for the duration of DDL emission. The
+ * deparse code in ruleutils (and the regclass/regproc output functions we
+ * transitively call) decides whether to schema-qualify a name based on
+ * whether the referenced object is reachable through search_path, so this
+ * makes same-schema references in DEFAULT and CHECK expressions, regclass
+ * literals, FK targets, etc. come out unqualified. Cross-schema
+ * references stay qualified, which is the correctness requirement.
+ * AtEOXact_GUC cleans up at xact end if anything throws between here and
+ * the explicit restore below; on the normal path we restore right before
+ * returning.
+ *
+ * Pre-compute "<schema>." too - the always-qualified helpers
+ * (constraintdef_command, indexdef_string, ruledef,
+ * statisticsobjdef_string) ignore search_path for the target's own "ALTER
+ * TABLE schema.tbl"/"ON schema.tbl"/"TO schema.tbl" prefix and for the
+ * qualified statistics name; we strip that string ourselves via
+ * strip_schema_prefix.
+ */
+ if (!ctx->schema_qualified)
+ {
+ char *nspname = get_namespace_name(ctx->base_namespace);
+
+ if (nspname != NULL)
+ {
+ const char *qnsp = quote_identifier(nspname);
+
+ ctx->schema_prefix = psprintf("%s.", qnsp);
+ ctx->save_nestlevel = NewGUCNestLevel();
+ (void) set_config_option("search_path", qnsp,
+ PGC_USERSET, PGC_S_SESSION,
+ GUC_ACTION_SAVE, true, 0, false);
+ pfree(nspname);
+ }
+ }
+
+ /*
+ * Cache locally-declared NOT NULL constraint metadata so the column- emit
+ * helpers can produce "CONSTRAINT name NOT NULL" inline for user-named
+ * constraints, and so the constraint loop can avoid double-emitting them.
+ */
+ ctx->nn_entries = collect_local_not_null(rel, &ctx->skip_notnull_oids);
+
+ initStringInfo(&ctx->buf);
+
+ /*
+ * Emission passes. Order is significant: CREATE TABLE first; OWNER and
+ * the per-column ALTER COLUMN passes before sub-object emission;
+ * sub-objects in dependency-friendly order (indexes before constraints,
+ * since constraint-backed indexes are emitted out-of-line by the
+ * constraint loop); partition children last so the parent already exists
+ * at replay time. Each helper gates itself on is_kind_included for the
+ * relevant TABLE_DDL_KIND_*; the four "table itself" passes are grouped
+ * here under KIND_TABLE so all of CREATE TABLE / OWNER / SET DEFAULT /
+ * SET (...) drop out together when the user asks for only sub-objects
+ * (e.g. include => 'foreign_keys' for the second pass of a two-pass FK
+ * clone).
+ */
+ if (is_kind_included(ctx, TABLE_DDL_KIND_TABLE))
+ {
+ emit_create_table_stmt(ctx);
+ emit_owner_stmt(ctx);
+ emit_child_default_overrides(ctx);
+ emit_attoptions(ctx);
+ }
+ emit_indexes(ctx);
+ emit_local_constraints(ctx);
+ emit_rules(ctx);
+ emit_statistics(ctx);
+ emit_replica_identity(ctx);
+ emit_rls_toggles(ctx);
+
+ /*
+ * Triggers and row-level security policies - disabled until the
+ * standalone pg_get_trigger_ddl() and pg_get_policy_ddl() helpers land.
+ * The scan + lock + filter scaffolding below is preserved (inside #if 0)
+ * so wiring up each emission becomes a one-liner in the loop body once
+ * those helpers are available.
+ */
+#if 0
+ if (is_kind_included(ctx, TABLE_DDL_KIND_TRIGGERS))
+ {
+ Relation trigRel;
+ SysScanDesc trigScan;
+ ScanKeyData trigKey;
+ HeapTuple trigTup;
+
+ trigRel = table_open(TriggerRelationId, AccessShareLock);
+ ScanKeyInit(&trigKey,
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ctx->relid));
+ trigScan = systable_beginscan(trigRel, TriggerRelidNameIndexId,
+ true, NULL, 1, &trigKey);
+ while (HeapTupleIsValid(trigTup = systable_getnext(trigScan)))
+ {
+ Form_pg_trigger trg = (Form_pg_trigger) GETSTRUCT(trigTup);
+
+ if (trg->tgisinternal)
+ continue;
+
+ /* TODO: append pg_get_trigger_ddl(trg->oid) output here. */
+ }
+ systable_endscan(trigScan);
+ table_close(trigRel, AccessShareLock);
+ }
+
+ if (is_kind_included(ctx, TABLE_DDL_KIND_POLICIES))
+ {
+ Relation polRel;
+ SysScanDesc polScan;
+ ScanKeyData polKey;
+ HeapTuple polTup;
+
+ polRel = table_open(PolicyRelationId, AccessShareLock);
+ ScanKeyInit(&polKey,
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ctx->relid));
+ polScan = systable_beginscan(polRel, PolicyPolrelidPolnameIndexId,
+ true, NULL, 1, &polKey);
+ while (HeapTupleIsValid(polTup = systable_getnext(polScan)))
+ {
+ /* TODO: append pg_get_policy_ddl(relid, polname) output here. */
+ }
+ systable_endscan(polScan);
+ table_close(polRel, AccessShareLock);
+ }
+#endif
+
+ emit_partition_children(ctx);
+
+ {
+ int natts = RelationGetDescr(rel)->natts;
+
+ for (int i = 1; i <= natts; i++)
+ if (ctx->nn_entries[i].name != NULL)
+ pfree(ctx->nn_entries[i].name);
+ }
+ pfree(ctx->nn_entries);
+
+ table_close(rel, AccessShareLock);
+ pfree(ctx->buf.data);
+ pfree(ctx->qualname);
+ if (ctx->schema_prefix != NULL)
+ pfree(ctx->schema_prefix);
+ list_free(ctx->skip_notnull_oids);
+
+ /*
+ * Pop the narrowed search_path now that all helpers have run. Errors
+ * thrown earlier are cleaned up by AtEOXact_GUC at xact end.
+ */
+ if (ctx->save_nestlevel >= 0)
+ AtEOXact_GUC(true, ctx->save_nestlevel);
+
+ return ctx->statements;
+}
+
+/*
+ * pg_get_table_ddl
+ * Return DDL to recreate a table as a set of text rows.
+ */
+Datum
+pg_get_table_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ TableDdlContext ctx = {0};
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ {"tablespace", DDL_OPT_BOOL},
+ {"schema_qualified", DDL_OPT_BOOL},
+ {"include", DDL_OPT_TEXT},
+ {"exclude", DDL_OPT_TEXT},
+ };
+ enum
+ {
+ OPT_PRETTY,
+ OPT_OWNER,
+ OPT_TABLESPACE,
+ OPT_SCHEMA_QUALIFIED,
+ OPT_INCLUDE,
+ OPT_EXCLUDE,
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ if (opts[OPT_INCLUDE].isset && opts[OPT_EXCLUDE].isset)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"include\" and \"exclude\" options are mutually exclusive")));
+
+ /*
+ * Initialize the option fields of the context to their defaults
+ * first, then override only those that the caller explicitly
+ * specified. Keeping the default values literal here (rather than
+ * folded into each opts[] lookup) makes the defaults obvious in one
+ * place and avoids the ternary-soup that made the previous
+ * positional-bool call site error-prone.
+ */
+ ctx.relid = PG_GETARG_OID(0);
+ ctx.pretty = false;
+ ctx.no_owner = false;
+ ctx.no_tablespace = false;
+ ctx.schema_qualified = true;
+ ctx.included_kinds = NULL;
+ ctx.excluded_kinds = NULL;
+
+ if (opts[OPT_PRETTY].isset)
+ ctx.pretty = opts[OPT_PRETTY].boolval;
+ if (opts[OPT_OWNER].isset)
+ ctx.no_owner = !opts[OPT_OWNER].boolval;
+ if (opts[OPT_TABLESPACE].isset)
+ ctx.no_tablespace = !opts[OPT_TABLESPACE].boolval;
+ if (opts[OPT_SCHEMA_QUALIFIED].isset)
+ ctx.schema_qualified = opts[OPT_SCHEMA_QUALIFIED].boolval;
+ if (opts[OPT_INCLUDE].isset)
+ ctx.included_kinds = parse_kind_list("include",
+ opts[OPT_INCLUDE].textval);
+ if (opts[OPT_EXCLUDE].isset)
+ ctx.excluded_kinds = parse_kind_list("exclude",
+ opts[OPT_EXCLUDE].textval);
+
+ statements = pg_get_table_ddl_internal(&ctx);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_inherits.h b/src/include/catalog/pg_inherits.h
index cc874abaabb..1e463b0106d 100644
--- a/src/include/catalog/pg_inherits.h
+++ b/src/include/catalog/pg_inherits.h
@@ -55,6 +55,7 @@ DECLARE_INDEX(pg_inherits_parent_index, 2187, InheritsParentIndexId, pg_inherits
extern List *find_inheritance_children(Oid parentrelId, LOCKMODE lockmode);
extern List *find_inheritance_children_extended(Oid parentrelId, bool omit_detached,
LOCKMODE lockmode, bool *detached_exist, TransactionId *detached_xmin);
+extern List *find_inheritance_parents(Oid relid, LOCKMODE lockmode);
extern List *find_all_inheritors(Oid parentrelId, LOCKMODE lockmode,
List **numparents);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..d13cf10eb43 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8615,6 +8615,13 @@
proargtypes => 'regdatabase text', proallargtypes => '{regdatabase,text}',
proargmodes => '{i,v}', proargdefaults => '{NULL}',
prosrc => 'pg_get_database_ddl' },
+{ oid => '8215', descr => 'get DDL to recreate a table',
+ proname => 'pg_get_table_ddl', prorows => '50', provariadic => 'text',
+ proisstrict => 'f', proretset => 't', provolatile => 's', proparallel => 'r',
+ pronargdefaults => '1', prorettype => 'text',
+ proargtypes => 'regclass _text', proallargtypes => '{regclass,_text}',
+ proargmodes => '{i,v}', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_table_ddl' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index c3d8518cb62..d33563fdead 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -92,6 +92,7 @@ extern void check_of_type(HeapTuple typetuple);
extern void register_on_commit_action(Oid relid, OnCommitAction action);
extern void remove_on_commit_action(Oid relid);
+extern OnCommitAction get_on_commit_action(Oid relid);
extern void PreCommit_on_commit_actions(void);
extern void AtEOXact_on_commit_actions(bool isCommit);
@@ -108,4 +109,6 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation,
extern bool PartConstraintImpliedByRelConstraint(Relation scanrel,
List *partConstraint);
+extern const char *storage_name(char c);
+
#endif /* TABLECMDS_H */
diff --git a/src/test/regress/expected/pg_get_table_ddl.out b/src/test/regress/expected/pg_get_table_ddl.out
new file mode 100644
index 00000000000..200aced7a73
--- /dev/null
+++ b/src/test/regress/expected/pg_get_table_ddl.out
@@ -0,0 +1,885 @@
+--
+-- pg_get_table_ddl
+--
+-- All tests pass owner=>false so the ALTER TABLE OWNER TO line is not
+-- emitted, keeping output stable across test runners (which may run under
+-- different role names).
+--
+CREATE SCHEMA pgtbl_ddl_test;
+SET search_path = pgtbl_ddl_test;
+-- Basic table with PRIMARY KEY, NOT NULL, DEFAULT, COLLATE.
+CREATE TABLE basic (
+ id int PRIMARY KEY,
+ name text NOT NULL DEFAULT 'anon' COLLATE "C"
+);
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+---------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.basic (id integer NOT NULL, name text COLLATE "C" DEFAULT 'anon'::text NOT NULL);
+ ALTER TABLE pgtbl_ddl_test.basic ADD CONSTRAINT basic_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- Identity columns (with default and custom sequence options).
+CREATE TABLE id_cols (
+ id_always int GENERATED ALWAYS AS IDENTITY,
+ id_default int GENERATED BY DEFAULT AS IDENTITY
+);
+SELECT * FROM pg_get_table_ddl('id_cols'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.id_cols (id_always integer GENERATED ALWAYS AS IDENTITY NOT NULL, id_default integer GENERATED BY DEFAULT AS IDENTITY NOT NULL);
+(1 row)
+
+CREATE TABLE id_custom (
+ v int GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME id_custom_v_seq
+ START WITH 100 INCREMENT BY 5
+ MINVALUE 50 MAXVALUE 1000
+ CACHE 10 CYCLE
+ )
+);
+SELECT * FROM pg_get_table_ddl('id_custom'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.id_custom (v integer GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 5 MINVALUE 50 MAXVALUE 1000 CACHE 10 CYCLE) NOT NULL);
+(1 row)
+
+-- Generated stored column.
+CREATE TABLE gen_cols (
+ cents int,
+ dollars numeric GENERATED ALWAYS AS (cents / 100.0) STORED
+);
+SELECT * FROM pg_get_table_ddl('gen_cols'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.gen_cols (cents integer, dollars numeric GENERATED ALWAYS AS (((cents)::numeric / 100.0)) STORED);
+(1 row)
+
+-- STORAGE and COMPRESSION (only emitted when non-default for the type).
+CREATE TABLE storage_cols (
+ a text STORAGE EXTERNAL,
+ b text STORAGE MAIN,
+ c text COMPRESSION pglz
+);
+SELECT * FROM pg_get_table_ddl('storage_cols'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.storage_cols (a text STORAGE EXTERNAL, b text STORAGE MAIN, c text COMPRESSION pglz);
+(1 row)
+
+-- Constraints: CHECK, UNIQUE, FOREIGN KEY (DEFERRABLE).
+CREATE TABLE refd (id int PRIMARY KEY);
+CREATE TABLE cons (
+ a int CHECK (a > 0),
+ b int UNIQUE,
+ c int REFERENCES refd(id) DEFERRABLE INITIALLY DEFERRED
+);
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer, CONSTRAINT cons_a_check CHECK ((a > 0)));
+ ALTER TABLE pgtbl_ddl_test.cons ADD CONSTRAINT cons_b_key UNIQUE (b);
+ ALTER TABLE pgtbl_ddl_test.cons ADD CONSTRAINT cons_c_fkey FOREIGN KEY (c) REFERENCES refd(id) DEFERRABLE INITIALLY DEFERRED;
+(3 rows)
+
+-- Indexes: functional and partial. Constraint-backing indexes are
+-- suppressed (they are emitted by the constraint loop).
+CREATE TABLE idxd (id int PRIMARY KEY, name text);
+CREATE INDEX idxd_lower ON idxd (lower(name));
+CREATE INDEX idxd_partial ON idxd (id) WHERE id > 100;
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.idxd (id integer NOT NULL, name text);
+ CREATE INDEX idxd_lower ON pgtbl_ddl_test.idxd USING btree (lower(name));
+ CREATE INDEX idxd_partial ON pgtbl_ddl_test.idxd USING btree (id) WHERE (id > 100);
+ ALTER TABLE pgtbl_ddl_test.idxd ADD CONSTRAINT idxd_pkey PRIMARY KEY (id);
+(4 rows)
+
+-- Inheritance, including a child DEFAULT override on an inherited column.
+CREATE TABLE par (a int DEFAULT 1, b text);
+CREATE TABLE ch (c int) INHERITS (par);
+ALTER TABLE ch ALTER COLUMN a SET DEFAULT 999;
+SELECT * FROM pg_get_table_ddl('ch'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+---------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ch (c integer) INHERITS (pgtbl_ddl_test.par);
+ ALTER TABLE pgtbl_ddl_test.ch ALTER COLUMN a SET DEFAULT 999;
+(2 rows)
+
+-- Per-column attoptions: emitted as ALTER COLUMN SET (...).
+CREATE TABLE attopt (a int, b text);
+ALTER TABLE attopt ALTER COLUMN a SET (n_distinct = 100);
+SELECT * FROM pg_get_table_ddl('attopt'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.attopt (a integer, b text);
+ ALTER TABLE pgtbl_ddl_test.attopt ALTER COLUMN a SET (n_distinct='100');
+(2 rows)
+
+-- Partitioned table parent (RANGE and HASH).
+CREATE TABLE parted_range (id int, k int) PARTITION BY RANGE (id);
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_range (id integer, k integer) PARTITION BY RANGE (id);
+(1 row)
+
+CREATE TABLE parted_hash (id int) PARTITION BY HASH (id);
+SELECT * FROM pg_get_table_ddl('parted_hash'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_hash (id integer) PARTITION BY HASH (id);
+(1 row)
+
+-- Partition children: FROM/TO, WITH (modulus, remainder), DEFAULT.
+CREATE TABLE parted_range_1 PARTITION OF parted_range
+ FOR VALUES FROM (0) TO (100);
+ALTER TABLE parted_range_1 ALTER COLUMN k SET DEFAULT 7;
+SELECT * FROM pg_get_table_ddl('parted_range_1'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_range_1 PARTITION OF pgtbl_ddl_test.parted_range FOR VALUES FROM (0) TO (100);
+ ALTER TABLE pgtbl_ddl_test.parted_range_1 ALTER COLUMN k SET DEFAULT 7;
+(2 rows)
+
+CREATE TABLE parted_hash_0 PARTITION OF parted_hash
+ FOR VALUES WITH (modulus 2, remainder 0);
+SELECT * FROM pg_get_table_ddl('parted_hash_0'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_hash_0 PARTITION OF pgtbl_ddl_test.parted_hash FOR VALUES WITH (modulus 2, remainder 0);
+(1 row)
+
+CREATE TABLE parted_range_def PARTITION OF parted_range DEFAULT;
+SELECT * FROM pg_get_table_ddl('parted_range_def'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_range_def PARTITION OF pgtbl_ddl_test.parted_range DEFAULT;
+(1 row)
+
+-- Rules.
+CREATE TABLE rt (id int);
+CREATE TABLE rt_log (id int);
+CREATE RULE rt_log_insert AS ON INSERT TO rt
+ DO ALSO INSERT INTO rt_log VALUES (NEW.id);
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+----------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rt (id integer);
+ CREATE RULE rt_log_insert AS +
+ ON INSERT TO pgtbl_ddl_test.rt DO INSERT INTO rt_log (id)+
+ VALUES (new.id);
+(2 rows)
+
+-- Extended statistics.
+CREATE TABLE stx (a int, b int, c int);
+CREATE STATISTICS stx_ndv (ndistinct) ON a, b FROM stx;
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.stx (a integer, b integer, c integer);
+ CREATE STATISTICS pgtbl_ddl_test.stx_ndv (ndistinct) ON a, b FROM stx;
+(2 rows)
+
+-- Row-level security toggles.
+CREATE TABLE rls (id int);
+ALTER TABLE rls ENABLE ROW LEVEL SECURITY;
+ALTER TABLE rls FORCE ROW LEVEL SECURITY;
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-----------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rls (id integer);
+ ALTER TABLE pgtbl_ddl_test.rls ENABLE ROW LEVEL SECURITY;
+ ALTER TABLE pgtbl_ddl_test.rls FORCE ROW LEVEL SECURITY;
+(3 rows)
+
+-- REPLICA IDENTITY: emitted only when not the default.
+CREATE TABLE ri_full (a int);
+ALTER TABLE ri_full REPLICA IDENTITY FULL;
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-----------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_full (a integer);
+ ALTER TABLE pgtbl_ddl_test.ri_full REPLICA IDENTITY FULL;
+(2 rows)
+
+CREATE TABLE ri_idx (a int NOT NULL);
+CREATE UNIQUE INDEX ri_idx_a ON ri_idx (a);
+ALTER TABLE ri_idx REPLICA IDENTITY USING INDEX ri_idx_a;
+SELECT * FROM pg_get_table_ddl('ri_idx'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_idx (a integer NOT NULL);
+ CREATE UNIQUE INDEX ri_idx_a ON pgtbl_ddl_test.ri_idx USING btree (a);
+ ALTER TABLE pgtbl_ddl_test.ri_idx REPLICA IDENTITY USING INDEX ri_idx_a;
+(3 rows)
+
+-- UNLOGGED + reloptions.
+CREATE UNLOGGED TABLE uno (id int) WITH (fillfactor = 70);
+SELECT * FROM pg_get_table_ddl('uno'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------
+ CREATE UNLOGGED TABLE pgtbl_ddl_test.uno (id integer) WITH (fillfactor='70');
+(1 row)
+
+-- Typed table (CREATE TABLE OF type_name). Columns inherited from the
+-- type emit nothing; locally-applied DEFAULT, NOT NULL and CHECK come
+-- out through a single "(col WITH OPTIONS ...)" list.
+CREATE TYPE typed_t AS (a int, b text);
+CREATE TABLE typed_plain OF typed_t;
+SELECT * FROM pg_get_table_ddl('typed_plain'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.typed_plain OF pgtbl_ddl_test.typed_t;
+(1 row)
+
+CREATE TABLE typed_over OF typed_t (
+ a WITH OPTIONS DEFAULT 7 NOT NULL,
+ b WITH OPTIONS NOT NULL,
+ CONSTRAINT b_nonempty CHECK (length(b) > 0)
+);
+SELECT * FROM pg_get_table_ddl('typed_over'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.typed_over OF pgtbl_ddl_test.typed_t (a WITH OPTIONS DEFAULT 7 NOT NULL, b WITH OPTIONS NOT NULL, CONSTRAINT b_nonempty CHECK ((length(b) > 0)));
+(1 row)
+
+-- Temporary tables + ON COMMIT. Temp tables live in a session-local
+-- namespace whose name varies between runs, so we strip the schema
+-- prefix. ON COMMIT DROP only fires at commit, so the queries that
+-- need to see the catalog entry must run in the same transaction as
+-- the CREATE.
+BEGIN;
+CREATE TEMP TABLE temp_default (id int);
+CREATE TEMP TABLE temp_delete (id int) ON COMMIT DELETE ROWS;
+CREATE TEMP TABLE temp_drop (id int) ON COMMIT DROP;
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_default'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+ line
+-----------------------------------------------------------
+ CREATE TEMPORARY TABLE pg_temp.temp_default (id integer);
+(1 row)
+
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_delete'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+ line
+--------------------------------------------------------------------------------
+ CREATE TEMPORARY TABLE pg_temp.temp_delete (id integer) ON COMMIT DELETE ROWS;
+(1 row)
+
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_drop'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+ line
+-----------------------------------------------------------------------
+ CREATE TEMPORARY TABLE pg_temp.temp_drop (id integer) ON COMMIT DROP;
+(1 row)
+
+ROLLBACK;
+-- Pretty mode.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false', 'pretty', 'true');
+ pg_get_table_ddl
+------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.basic ( +
+ id integer NOT NULL, +
+ name text COLLATE "C" DEFAULT 'anon'::text NOT NULL +
+ );
+ ALTER TABLE pgtbl_ddl_test.basic ADD CONSTRAINT basic_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- VARIADIC ARRAY[...] call form: option pairs can be passed as a single
+-- text[] tagged with VARIADIC, in addition to the positional spelling
+-- exercised above. This requires proargtypes to declare the variadic
+-- argument as the array type (_text).
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ VARIADIC ARRAY['owner', 'false']);
+ pg_get_table_ddl
+---------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.basic (id integer NOT NULL, name text COLLATE "C" DEFAULT 'anon'::text NOT NULL);
+ ALTER TABLE pgtbl_ddl_test.basic ADD CONSTRAINT basic_pkey PRIMARY KEY (id);
+(2 rows)
+
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ VARIADIC ARRAY['owner', 'false',
+ 'pretty', 'true']::text[]);
+ pg_get_table_ddl
+------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.basic ( +
+ id integer NOT NULL, +
+ name text COLLATE "C" DEFAULT 'anon'::text NOT NULL +
+ );
+ ALTER TABLE pgtbl_ddl_test.basic ADD CONSTRAINT basic_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- VARIADIC NULL::text[] is equivalent to omitting the variadic arguments;
+-- check the row count rather than the rows themselves, because without
+-- 'owner', 'false' the output contains an ALTER TABLE ... OWNER TO line
+-- that varies across test runners.
+SELECT count(*) > 0 AS ok
+ FROM pg_get_table_ddl('basic'::regclass, VARIADIC NULL::text[]);
+ ok
+----
+ t
+(1 row)
+
+-- include / exclude gating: each emits either only the listed kinds
+-- (include) or every kind except the listed ones (exclude). The two
+-- are mutually exclusive. Kind vocabulary: table, indexes,
+-- primary_key, unique, check, foreign_keys, exclusion, rules,
+-- statistics, triggers, policies, rls, replica_identity, partitions.
+-- NOT NULL is not in the vocabulary - always emitted to prevent
+-- producing schemas that silently accept NULLs the source would
+-- have rejected.
+--
+-- exclude=indexes hides the CREATE INDEX statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'exclude', 'indexes');
+ pg_get_table_ddl
+----------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.idxd (id integer NOT NULL, name text);
+ ALTER TABLE pgtbl_ddl_test.idxd ADD CONSTRAINT idxd_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- exclude=primary_key,unique,check,foreign_keys,exclusion hides every
+-- table-level constraint. Inline CHECK in the CREATE TABLE body is
+-- suppressed too.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude',
+ 'primary_key,unique,check,foreign_keys,exclusion');
+ pg_get_table_ddl
+---------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer);
+(1 row)
+
+-- exclude=foreign_keys suppresses only FOREIGN KEY constraints;
+-- PRIMARY KEY, UNIQUE, CHECK, and named NOT NULL stay. This is the
+-- first pass of the two-pass FK-clone workflow: emit tables without
+-- cross-table FKs, load data, then re-run with include=foreign_keys
+-- to add them once all targets exist.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude', 'foreign_keys');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer, CONSTRAINT cons_a_check CHECK ((a > 0)));
+ ALTER TABLE pgtbl_ddl_test.cons ADD CONSTRAINT cons_b_key UNIQUE (b);
+(2 rows)
+
+-- include=foreign_keys is the second pass of the two-pass FK-clone
+-- workflow: emit only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN
+-- KEY rows; the CREATE TABLE and every non-FK sub-object are
+-- suppressed (the table already exists from the first pass).
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.cons ADD CONSTRAINT cons_c_fkey FOREIGN KEY (c) REFERENCES refd(id) DEFERRABLE INITIALLY DEFERRED;
+(1 row)
+
+-- A table with no foreign keys produces no rows under
+-- include=foreign_keys.
+SELECT * FROM pg_get_table_ddl('refd'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys');
+ pg_get_table_ddl
+------------------
+(0 rows)
+
+-- include=table emits only the bare CREATE TABLE (with any inline
+-- column-level NOT NULL and CHECK) - no indexes, no PRIMARY KEY ALTER,
+-- no other sub-objects. Useful for capturing a table's shape without
+-- pulling in everything attached to it.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'include', 'table');
+ pg_get_table_ddl
+--------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.idxd (id integer NOT NULL, name text);
+(1 row)
+
+-- include and exclude are mutually exclusive.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys',
+ 'exclude', 'foreign_keys');
+ERROR: "include" and "exclude" options are mutually exclusive
+-- Pub/sub schema clone: keep the table and its primary key, drop the
+-- rest of the constraints so the subscriber can replicate without
+-- pulling cross-table dependencies along.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude',
+ 'unique,check,foreign_keys,exclusion');
+ pg_get_table_ddl
+---------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer);
+(1 row)
+
+-- exclude=rules hides the CREATE RULE.
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'exclude', 'rules');
+ pg_get_table_ddl
+----------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rt (id integer);
+(1 row)
+
+-- exclude=statistics hides the CREATE STATISTICS.
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'exclude', 'statistics');
+ pg_get_table_ddl
+--------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.stx (a integer, b integer, c integer);
+(1 row)
+
+-- exclude=rls hides the ENABLE/FORCE ROW LEVEL SECURITY toggles.
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'exclude', 'rls');
+ pg_get_table_ddl
+-----------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rls (id integer);
+(1 row)
+
+-- exclude=replica_identity hides the REPLICA IDENTITY clause.
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'exclude', 'replica_identity');
+ pg_get_table_ddl
+--------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_full (a integer);
+(1 row)
+
+-- Partition children are emitted by default; exclude=partitions
+-- suppresses them so only the partitioned-table parent comes out.
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false',
+ 'exclude', 'partitions');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_range (id integer, k integer) PARTITION BY RANGE (id);
+(1 row)
+
+-- Unknown kind name is rejected at option-parse time.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'exclude', 'no_such_kind');
+ERROR: unrecognized kind "no_such_kind" in option "exclude"
+-- Whitespace around kind names is allowed; case is folded.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'exclude', ' INDEXES , RULES ');
+ pg_get_table_ddl
+----------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.idxd (id integer NOT NULL, name text);
+ ALTER TABLE pgtbl_ddl_test.idxd ADD CONSTRAINT idxd_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- schema_qualified=false strips the schema from the target table itself
+-- and from same-schema sibling references (inheritance/partition
+-- parents, identity sequences in the same schema), while cross-schema
+-- references are kept qualified for correctness.
+--
+-- Bare CREATE TABLE for an ordinary table.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+------------------------------------------------------------------------------------------------
+ CREATE TABLE basic (id integer NOT NULL, name text COLLATE "C" DEFAULT 'anon'::text NOT NULL);
+ ALTER TABLE basic ADD CONSTRAINT basic_pkey PRIMARY KEY (id);
+(2 rows)
+
+-- INHERITS parent in the same schema is emitted unqualified.
+SELECT * FROM pg_get_table_ddl('ch'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+------------------------------------------------
+ CREATE TABLE ch (c integer) INHERITS (par);
+ ALTER TABLE ch ALTER COLUMN a SET DEFAULT 999;
+(2 rows)
+
+-- Partition parent in the same schema is emitted unqualified.
+SELECT * FROM pg_get_table_ddl('parted_range_1'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------
+ CREATE TABLE parted_range_1 PARTITION OF parted_range FOR VALUES FROM (0) TO (100);
+ ALTER TABLE parted_range_1 ALTER COLUMN k SET DEFAULT 7;
+(2 rows)
+
+-- Cross-schema FK target keeps qualification. The 'refd' table is in
+-- pgtbl_ddl_test; place an FK target in a different schema and verify it
+-- stays qualified even with schema_qualified=false.
+CREATE SCHEMA pgtbl_ddl_other;
+CREATE TABLE pgtbl_ddl_other.parent (id int PRIMARY KEY);
+CREATE TABLE xschema_fk (
+ id int PRIMARY KEY,
+ pid int REFERENCES pgtbl_ddl_other.parent
+);
+SELECT * FROM pg_get_table_ddl('xschema_fk'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE xschema_fk (id integer NOT NULL, pid integer);
+ ALTER TABLE xschema_fk ADD CONSTRAINT xschema_fk_pid_fkey FOREIGN KEY (pid) REFERENCES pgtbl_ddl_other.parent(id);
+ ALTER TABLE xschema_fk ADD CONSTRAINT xschema_fk_pkey PRIMARY KEY (id);
+(3 rows)
+
+-- Identity column with a custom sequence name in the same schema is
+-- emitted unqualified.
+SELECT * FROM pg_get_table_ddl('id_custom'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE id_custom (v integer GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 5 MINVALUE 50 MAXVALUE 1000 CACHE 10 CYCLE) NOT NULL);
+(1 row)
+
+-- Round-trip: with schema_qualified=false, after SET search_path the
+-- DDL can be replayed into a different target schema.
+CREATE SCHEMA pgtbl_ddl_replay;
+DO $$
+DECLARE
+ stmt text;
+BEGIN
+ SET LOCAL search_path = pgtbl_ddl_replay;
+ FOR stmt IN
+ SELECT line FROM pg_get_table_ddl('pgtbl_ddl_test.basic'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false') AS line
+ LOOP
+ EXECUTE stmt;
+ END LOOP;
+END $$;
+SELECT relnamespace::regnamespace::text, relname
+FROM pg_class WHERE oid = 'pgtbl_ddl_replay.basic'::regclass;
+ relnamespace | relname
+------------------+---------
+ pgtbl_ddl_replay | basic
+(1 row)
+
+DROP SCHEMA pgtbl_ddl_replay CASCADE;
+NOTICE: drop cascades to table pgtbl_ddl_replay.basic
+DROP TABLE xschema_fk;
+DROP SCHEMA pgtbl_ddl_other CASCADE;
+NOTICE: drop cascades to table pgtbl_ddl_other.parent
+-- Locally-declared CHECK on a partition child has no inline column
+-- list to live in (PARTITION OF form), so it must come out as a
+-- separate ALTER TABLE ... ADD CONSTRAINT statement.
+CREATE TABLE parted_chk (id int, val int) PARTITION BY RANGE (id);
+CREATE TABLE parted_chk_child PARTITION OF parted_chk
+ (CONSTRAINT chk_inline CHECK (val > 0))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('parted_chk_child'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.parted_chk_child PARTITION OF pgtbl_ddl_test.parted_chk FOR VALUES FROM (0) TO (100);
+ ALTER TABLE pgtbl_ddl_test.parted_chk_child ADD CONSTRAINT chk_inline CHECK ((val > 0));
+(2 rows)
+
+DROP TABLE parted_chk;
+-- Inheritance children of a parent with a generated column should not
+-- emit an ALTER COLUMN ... SET DEFAULT for the inherited generated
+-- column: the GENERATED expression carries down through inheritance,
+-- and SET DEFAULT would fail at replay against a generated column.
+CREATE TABLE par_gen (
+ id int,
+ g int GENERATED ALWAYS AS (id * 2) STORED
+);
+CREATE TABLE ch_gen () INHERITS (par_gen);
+SELECT * FROM pg_get_table_ddl('ch_gen'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+--------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ch_gen () INHERITS (pgtbl_ddl_test.par_gen);
+(1 row)
+
+DROP TABLE par_gen CASCADE;
+NOTICE: drop cascades to table ch_gen
+-- A user-named NOT NULL on a local column is emitted inline as
+-- "CONSTRAINT name NOT NULL" so the original name is preserved. An
+-- auto-named NOT NULL on a local column is emitted as a plain inline
+-- "NOT NULL" - PG re-creates the auto-named constraint when CREATE
+-- TABLE runs, so the constraint loop skips both forms to avoid the
+-- redundant (and on table-rename or sequence-collision, broken)
+-- ALTER TABLE ... ADD CONSTRAINT ... NOT NULL statement.
+CREATE TABLE nn_named (
+ a int CONSTRAINT my_nn NOT NULL,
+ b int NOT NULL
+);
+SELECT * FROM pg_get_table_ddl('nn_named'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.nn_named (a integer CONSTRAINT my_nn NOT NULL, b integer NOT NULL);
+(1 row)
+
+DROP TABLE nn_named;
+-- Renaming the table after an IDENTITY NOT NULL column was declared
+-- leaves the auto-named constraint frozen at the original name, which
+-- no longer matches the post-rename "<table>_<col>_not_null" pattern.
+-- The emitted DDL must still round-trip: the constraint loop must not
+-- ALTER TABLE ... ADD CONSTRAINT a second NOT NULL, since CREATE TABLE
+-- with the inline IDENTITY already creates an auto-NOT-NULL under the
+-- new table name and PG only permits one NOT NULL per column.
+CREATE TABLE nn_renamed (
+ a int GENERATED ALWAYS AS IDENTITY NOT NULL
+);
+ALTER TABLE nn_renamed RENAME TO nn_renamed2;
+SELECT * FROM pg_get_table_ddl('nn_renamed2'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.nn_renamed2 (a integer GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME pgtbl_ddl_test.nn_renamed_a_seq) CONSTRAINT nn_renamed_a_not_null NOT NULL);
+(1 row)
+
+DROP TABLE nn_renamed2;
+-- Locally-declared NOT NULL on an inherited column. The column itself
+-- is omitted from the child's column list (attislocal=false), so the
+-- user-named NOT NULL must come out via the post-CREATE constraint
+-- loop rather than inline.
+CREATE TABLE nn_par (a int);
+CREATE TABLE nn_ch (b int) INHERITS (nn_par);
+ALTER TABLE nn_ch ADD CONSTRAINT my_nn NOT NULL a;
+SELECT * FROM pg_get_table_ddl('nn_ch'::regclass, 'owner', 'false');
+ pg_get_table_ddl
+---------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.nn_ch (b integer) INHERITS (pgtbl_ddl_test.nn_par);
+ ALTER TABLE pgtbl_ddl_test.nn_ch ADD CONSTRAINT my_nn NOT NULL a;
+(2 rows)
+
+DROP TABLE nn_par CASCADE;
+NOTICE: drop cascades to table nn_ch
+-- schema_qualified=false must drop the schema prefix from DEFAULT
+-- expressions and inline CHECK bodies that reference same-schema
+-- objects (sequence via nextval, function via direct call). This
+-- relies on the deparser respecting the narrowed search_path rather
+-- than on substring stripping.
+CREATE SCHEMA pgtbl_ddl_xref;
+CREATE SEQUENCE pgtbl_ddl_xref.myseq;
+CREATE FUNCTION pgtbl_ddl_xref.f(int) RETURNS int LANGUAGE sql IMMUTABLE
+ AS 'SELECT $1';
+CREATE TABLE pgtbl_ddl_xref.t (
+ id int DEFAULT nextval('pgtbl_ddl_xref.myseq'),
+ val int,
+ CONSTRAINT chk CHECK (pgtbl_ddl_xref.f(val) > 0)
+);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_xref.t'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE t (id integer DEFAULT nextval('myseq'::regclass), val integer, CONSTRAINT chk CHECK ((f(val) > 0)));
+(1 row)
+
+DROP SCHEMA pgtbl_ddl_xref CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to sequence pgtbl_ddl_xref.myseq
+drop cascades to function pgtbl_ddl_xref.f(integer)
+drop cascades to table pgtbl_ddl_xref.t
+-- schema_qualified=false must NOT corrupt string literals whose
+-- contents happen to contain the schema name followed by a dot. The
+-- in-string state of the prefix stripper preserves them verbatim.
+CREATE SCHEMA pgtbl_ddl_str;
+CREATE TABLE pgtbl_ddl_str.p (id int, note text) PARTITION BY RANGE (id);
+CREATE TABLE pgtbl_ddl_str.pc PARTITION OF pgtbl_ddl_str.p
+ (CONSTRAINT chk CHECK (note <> 'pgtbl_ddl_str.secret'))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_str.pc'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+-----------------------------------------------------------------------------------
+ CREATE TABLE pc PARTITION OF p FOR VALUES FROM (0) TO (100);
+ ALTER TABLE pc ADD CONSTRAINT chk CHECK ((note <> 'pgtbl_ddl_str.secret'::text));
+(2 rows)
+
+DROP SCHEMA pgtbl_ddl_str CASCADE;
+NOTICE: drop cascades to table pgtbl_ddl_str.p
+-- schema_qualified=false must NOT corrupt double-quoted identifiers
+-- whose contents happen to contain the schema name followed by a dot
+-- (for example a column literally named "<schema>.weird" inside that
+-- schema). The in-ident state of the prefix stripper preserves them
+-- verbatim.
+CREATE SCHEMA pgtbl_ddl_qid;
+CREATE TABLE pgtbl_ddl_qid.p (id int, "pgtbl_ddl_qid.weird" int)
+ PARTITION BY RANGE (id);
+CREATE TABLE pgtbl_ddl_qid.pc PARTITION OF pgtbl_ddl_qid.p
+ (CONSTRAINT chk CHECK ("pgtbl_ddl_qid.weird" > 0))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_qid.pc'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+------------------------------------------------------------------------
+ CREATE TABLE pc PARTITION OF p FOR VALUES FROM (0) TO (100);
+ ALTER TABLE pc ADD CONSTRAINT chk CHECK (("pgtbl_ddl_qid.weird" > 0));
+(2 rows)
+
+DROP SCHEMA pgtbl_ddl_qid CASCADE;
+NOTICE: drop cascades to table pgtbl_ddl_qid.p
+-- schema_qualified=false when the base schema name itself requires
+-- quoting (its prefix starts with "). Both forms of the prefix -
+-- bare-lowercase and quoted - must be stripped from outer
+-- ALTER TABLE / ON / CREATE INDEX prefixes, while inner quoted
+-- identifiers that happen to begin the same way must be preserved.
+CREATE SCHEMA "PgTbl-Ddl-Q";
+CREATE TABLE "PgTbl-Ddl-Q"."T" (id int, val int,
+ CONSTRAINT chk CHECK (val > 0));
+CREATE INDEX "T_val_idx" ON "PgTbl-Ddl-Q"."T" (val);
+SELECT * FROM pg_get_table_ddl('"PgTbl-Ddl-Q"."T"'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------
+ CREATE TABLE "T" (id integer, val integer, CONSTRAINT chk CHECK ((val > 0)));
+ CREATE INDEX "T_val_idx" ON "T" USING btree (val);
+(2 rows)
+
+DROP SCHEMA "PgTbl-Ddl-Q" CASCADE;
+NOTICE: drop cascades to table "PgTbl-Ddl-Q"."T"
+-- Error: not an ordinary or partitioned table.
+CREATE VIEW v AS SELECT 1 AS x;
+SELECT * FROM pg_get_table_ddl('v'::regclass);
+ERROR: "v" is not an ordinary or partitioned table
+CREATE SEQUENCE s;
+SELECT * FROM pg_get_table_ddl('s'::regclass);
+ERROR: "s" is not an ordinary or partitioned table
+-- NULL argument returns no rows.
+SELECT * FROM pg_get_table_ddl(NULL);
+ pg_get_table_ddl
+------------------
+(0 rows)
+
+-- Unrecognized option.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'bogus', 'true');
+ERROR: unrecognized option: "bogus"
+-- Odd number of variadic args.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'pretty');
+ERROR: variadic arguments must be name/value pairs
+HINT: Provide an even number of variadic arguments that can be divided into pairs.
+-- exclude=primary_key on a table whose REPLICA IDENTITY references
+-- the PK would emit a REPLICA IDENTITY USING INDEX clause that
+-- referenced an index the same DDL just dropped. Require the user
+-- to also exclude replica_identity in that case.
+CREATE TABLE ri_pk_excl (a int PRIMARY KEY);
+ALTER TABLE ri_pk_excl REPLICA IDENTITY USING INDEX ri_pk_excl_pkey;
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'exclude', 'primary_key');
+ERROR: cannot exclude "primary_key" without also excluding "replica_identity" for table "ri_pk_excl"
+DETAIL: The table's REPLICA IDENTITY references the primary key index.
+-- Same call but also excluding replica_identity succeeds.
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'exclude', 'primary_key,replica_identity');
+ pg_get_table_ddl
+--------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_pk_excl (a integer NOT NULL);
+(1 row)
+
+DROP TABLE ri_pk_excl;
+-- Round-trip verification. For every test table, capture the DDL the
+-- function emits, drop the schema, replay each table's DDL in
+-- dependency order, and confirm that pg_get_table_ddl on the recreated
+-- relation matches the original line-for-line. The final SELECT must
+-- return zero rows.
+CREATE TEMP TABLE pgtbl_ddl_rt_orig (name text, ord int, line text);
+INSERT INTO pgtbl_ddl_rt_orig
+SELECT t.name, o.ord, o.line
+FROM (VALUES
+ ('basic'), ('id_cols'), ('id_custom'), ('gen_cols'), ('storage_cols'),
+ ('refd'), ('cons'), ('idxd'),
+ ('par'), ('ch'), ('attopt'),
+ ('parted_range'), ('parted_range_1'), ('parted_range_def'),
+ ('parted_hash'), ('parted_hash_0'),
+ ('rt_log'), ('rt'),
+ ('stx'), ('rls'), ('ri_full'), ('ri_idx'), ('uno')
+ ) AS t(name),
+ LATERAL pg_get_table_ddl(('pgtbl_ddl_test.' || t.name)::regclass,
+ 'owner', 'false') WITH ORDINALITY o(line, ord);
+DO $$
+DECLARE
+ tables CONSTANT text[] := ARRAY[
+ 'basic', 'id_cols', 'id_custom', 'gen_cols', 'storage_cols',
+ 'refd', 'cons', 'idxd',
+ 'par', 'ch', 'attopt',
+ 'parted_range', 'parted_range_1', 'parted_range_def',
+ 'parted_hash', 'parted_hash_0',
+ 'rt_log', 'rt',
+ 'stx', 'rls', 'ri_full', 'ri_idx', 'uno'
+ ];
+ t text;
+ stmt text;
+BEGIN
+ DROP SCHEMA pgtbl_ddl_test CASCADE;
+ CREATE SCHEMA pgtbl_ddl_test;
+ FOREACH t IN ARRAY tables LOOP
+ FOR stmt IN
+ SELECT line FROM pgtbl_ddl_rt_orig WHERE name = t ORDER BY ord
+ LOOP
+ EXECUTE stmt;
+ END LOOP;
+ END LOOP;
+END $$;
+NOTICE: drop cascades to 25 other objects
+DETAIL: drop cascades to table basic
+drop cascades to table id_cols
+drop cascades to table id_custom
+drop cascades to table gen_cols
+drop cascades to table storage_cols
+drop cascades to table refd
+drop cascades to table cons
+drop cascades to table idxd
+drop cascades to table par
+drop cascades to table ch
+drop cascades to table attopt
+drop cascades to table parted_range
+drop cascades to table parted_hash
+drop cascades to table rt
+drop cascades to table rt_log
+drop cascades to table stx
+drop cascades to table rls
+drop cascades to table ri_full
+drop cascades to table ri_idx
+drop cascades to table uno
+drop cascades to type typed_t
+drop cascades to table typed_plain
+drop cascades to table typed_over
+drop cascades to view v
+drop cascades to sequence s
+ERROR: relation "parted_range_1" already exists
+CONTEXT: SQL statement "CREATE TABLE pgtbl_ddl_test.parted_range_1 PARTITION OF pgtbl_ddl_test.parted_range FOR VALUES FROM (0) TO (100);"
+PL/pgSQL function inline_code_block line 21 at EXECUTE
+WITH after_ddl AS (
+ SELECT t.name, o.ord, o.line
+ FROM (SELECT DISTINCT name FROM pgtbl_ddl_rt_orig) AS t,
+ LATERAL pg_get_table_ddl(('pgtbl_ddl_test.' || t.name)::regclass,
+ 'owner', 'false') WITH ORDINALITY o(line, ord)
+)
+(SELECT 'missing-in-copy' AS kind, name, ord, line FROM pgtbl_ddl_rt_orig
+ EXCEPT
+ SELECT 'missing-in-copy', name, ord, line FROM after_ddl)
+UNION ALL
+(SELECT 'extra-in-copy' AS kind, name, ord, line FROM after_ddl
+ EXCEPT
+ SELECT 'extra-in-copy', name, ord, line FROM pgtbl_ddl_rt_orig)
+ORDER BY kind, name, ord;
+ kind | name | ord | line
+------+------+-----+------
+(0 rows)
+
+-- Cleanup.
+DROP SCHEMA pgtbl_ddl_test CASCADE;
+NOTICE: drop cascades to 25 other objects
+DETAIL: drop cascades to table basic
+drop cascades to table id_cols
+drop cascades to table id_custom
+drop cascades to table gen_cols
+drop cascades to table storage_cols
+drop cascades to table refd
+drop cascades to table cons
+drop cascades to table idxd
+drop cascades to table par
+drop cascades to table ch
+drop cascades to table attopt
+drop cascades to table parted_range
+drop cascades to table parted_hash
+drop cascades to table rt
+drop cascades to table rt_log
+drop cascades to table stx
+drop cascades to table rls
+drop cascades to table ri_full
+drop cascades to table ri_idx
+drop cascades to table uno
+drop cascades to type typed_t
+drop cascades to table typed_plain
+drop cascades to table typed_over
+drop cascades to view v
+drop cascades to sequence s
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..9eca64cca3b 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -81,7 +81,7 @@ test: create_table_like alter_generic alter_operator misc async dbsize merge mis
# collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with each other
# psql depends on create_am
# amutils depends on geometry, create_index_spgist, hash_index, brin
-test: rules psql psql_crosstab psql_pipeline amutils stats_ext collate.linux.utf8 collate.windows.win1252
+test: rules psql psql_crosstab psql_pipeline amutils stats_ext collate.linux.utf8 collate.windows.win1252 pg_get_table_ddl
# ----------
# Run these alone so they don't run out of parallel workers
diff --git a/src/test/regress/sql/pg_get_table_ddl.sql b/src/test/regress/sql/pg_get_table_ddl.sql
new file mode 100644
index 00000000000..9e332194db0
--- /dev/null
+++ b/src/test/regress/sql/pg_get_table_ddl.sql
@@ -0,0 +1,542 @@
+--
+-- pg_get_table_ddl
+--
+-- All tests pass owner=>false so the ALTER TABLE OWNER TO line is not
+-- emitted, keeping output stable across test runners (which may run under
+-- different role names).
+--
+CREATE SCHEMA pgtbl_ddl_test;
+SET search_path = pgtbl_ddl_test;
+
+-- Basic table with PRIMARY KEY, NOT NULL, DEFAULT, COLLATE.
+CREATE TABLE basic (
+ id int PRIMARY KEY,
+ name text NOT NULL DEFAULT 'anon' COLLATE "C"
+);
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false');
+
+-- Identity columns (with default and custom sequence options).
+CREATE TABLE id_cols (
+ id_always int GENERATED ALWAYS AS IDENTITY,
+ id_default int GENERATED BY DEFAULT AS IDENTITY
+);
+SELECT * FROM pg_get_table_ddl('id_cols'::regclass, 'owner', 'false');
+
+CREATE TABLE id_custom (
+ v int GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME id_custom_v_seq
+ START WITH 100 INCREMENT BY 5
+ MINVALUE 50 MAXVALUE 1000
+ CACHE 10 CYCLE
+ )
+);
+SELECT * FROM pg_get_table_ddl('id_custom'::regclass, 'owner', 'false');
+
+-- Generated stored column.
+CREATE TABLE gen_cols (
+ cents int,
+ dollars numeric GENERATED ALWAYS AS (cents / 100.0) STORED
+);
+SELECT * FROM pg_get_table_ddl('gen_cols'::regclass, 'owner', 'false');
+
+-- STORAGE and COMPRESSION (only emitted when non-default for the type).
+CREATE TABLE storage_cols (
+ a text STORAGE EXTERNAL,
+ b text STORAGE MAIN,
+ c text COMPRESSION pglz
+);
+SELECT * FROM pg_get_table_ddl('storage_cols'::regclass, 'owner', 'false');
+
+-- Constraints: CHECK, UNIQUE, FOREIGN KEY (DEFERRABLE).
+CREATE TABLE refd (id int PRIMARY KEY);
+CREATE TABLE cons (
+ a int CHECK (a > 0),
+ b int UNIQUE,
+ c int REFERENCES refd(id) DEFERRABLE INITIALLY DEFERRED
+);
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false');
+
+-- Indexes: functional and partial. Constraint-backing indexes are
+-- suppressed (they are emitted by the constraint loop).
+CREATE TABLE idxd (id int PRIMARY KEY, name text);
+CREATE INDEX idxd_lower ON idxd (lower(name));
+CREATE INDEX idxd_partial ON idxd (id) WHERE id > 100;
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false');
+
+-- Inheritance, including a child DEFAULT override on an inherited column.
+CREATE TABLE par (a int DEFAULT 1, b text);
+CREATE TABLE ch (c int) INHERITS (par);
+ALTER TABLE ch ALTER COLUMN a SET DEFAULT 999;
+SELECT * FROM pg_get_table_ddl('ch'::regclass, 'owner', 'false');
+
+-- Per-column attoptions: emitted as ALTER COLUMN SET (...).
+CREATE TABLE attopt (a int, b text);
+ALTER TABLE attopt ALTER COLUMN a SET (n_distinct = 100);
+SELECT * FROM pg_get_table_ddl('attopt'::regclass, 'owner', 'false');
+
+-- Partitioned table parent (RANGE and HASH).
+CREATE TABLE parted_range (id int, k int) PARTITION BY RANGE (id);
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false');
+
+CREATE TABLE parted_hash (id int) PARTITION BY HASH (id);
+SELECT * FROM pg_get_table_ddl('parted_hash'::regclass, 'owner', 'false');
+
+-- Partition children: FROM/TO, WITH (modulus, remainder), DEFAULT.
+CREATE TABLE parted_range_1 PARTITION OF parted_range
+ FOR VALUES FROM (0) TO (100);
+ALTER TABLE parted_range_1 ALTER COLUMN k SET DEFAULT 7;
+SELECT * FROM pg_get_table_ddl('parted_range_1'::regclass, 'owner', 'false');
+
+CREATE TABLE parted_hash_0 PARTITION OF parted_hash
+ FOR VALUES WITH (modulus 2, remainder 0);
+SELECT * FROM pg_get_table_ddl('parted_hash_0'::regclass, 'owner', 'false');
+
+CREATE TABLE parted_range_def PARTITION OF parted_range DEFAULT;
+SELECT * FROM pg_get_table_ddl('parted_range_def'::regclass, 'owner', 'false');
+
+-- Rules.
+CREATE TABLE rt (id int);
+CREATE TABLE rt_log (id int);
+CREATE RULE rt_log_insert AS ON INSERT TO rt
+ DO ALSO INSERT INTO rt_log VALUES (NEW.id);
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false');
+
+-- Extended statistics.
+CREATE TABLE stx (a int, b int, c int);
+CREATE STATISTICS stx_ndv (ndistinct) ON a, b FROM stx;
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false');
+
+-- Row-level security toggles.
+CREATE TABLE rls (id int);
+ALTER TABLE rls ENABLE ROW LEVEL SECURITY;
+ALTER TABLE rls FORCE ROW LEVEL SECURITY;
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false');
+
+-- REPLICA IDENTITY: emitted only when not the default.
+CREATE TABLE ri_full (a int);
+ALTER TABLE ri_full REPLICA IDENTITY FULL;
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false');
+
+CREATE TABLE ri_idx (a int NOT NULL);
+CREATE UNIQUE INDEX ri_idx_a ON ri_idx (a);
+ALTER TABLE ri_idx REPLICA IDENTITY USING INDEX ri_idx_a;
+SELECT * FROM pg_get_table_ddl('ri_idx'::regclass, 'owner', 'false');
+
+-- UNLOGGED + reloptions.
+CREATE UNLOGGED TABLE uno (id int) WITH (fillfactor = 70);
+SELECT * FROM pg_get_table_ddl('uno'::regclass, 'owner', 'false');
+
+-- Typed table (CREATE TABLE OF type_name). Columns inherited from the
+-- type emit nothing; locally-applied DEFAULT, NOT NULL and CHECK come
+-- out through a single "(col WITH OPTIONS ...)" list.
+CREATE TYPE typed_t AS (a int, b text);
+CREATE TABLE typed_plain OF typed_t;
+SELECT * FROM pg_get_table_ddl('typed_plain'::regclass, 'owner', 'false');
+
+CREATE TABLE typed_over OF typed_t (
+ a WITH OPTIONS DEFAULT 7 NOT NULL,
+ b WITH OPTIONS NOT NULL,
+ CONSTRAINT b_nonempty CHECK (length(b) > 0)
+);
+SELECT * FROM pg_get_table_ddl('typed_over'::regclass, 'owner', 'false');
+
+-- Temporary tables + ON COMMIT. Temp tables live in a session-local
+-- namespace whose name varies between runs, so we strip the schema
+-- prefix. ON COMMIT DROP only fires at commit, so the queries that
+-- need to see the catalog entry must run in the same transaction as
+-- the CREATE.
+BEGIN;
+CREATE TEMP TABLE temp_default (id int);
+CREATE TEMP TABLE temp_delete (id int) ON COMMIT DELETE ROWS;
+CREATE TEMP TABLE temp_drop (id int) ON COMMIT DROP;
+
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_default'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_delete'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+
+SELECT regexp_replace(line, '"?pg_temp_?[0-9]+"?\.', 'pg_temp.') AS line
+FROM pg_get_table_ddl('temp_drop'::regclass, 'owner', 'false') AS line
+WHERE line LIKE 'CREATE %';
+ROLLBACK;
+
+-- Pretty mode.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false', 'pretty', 'true');
+
+-- VARIADIC ARRAY[...] call form: option pairs can be passed as a single
+-- text[] tagged with VARIADIC, in addition to the positional spelling
+-- exercised above. This requires proargtypes to declare the variadic
+-- argument as the array type (_text).
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ VARIADIC ARRAY['owner', 'false']);
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ VARIADIC ARRAY['owner', 'false',
+ 'pretty', 'true']::text[]);
+
+-- VARIADIC NULL::text[] is equivalent to omitting the variadic arguments;
+-- check the row count rather than the rows themselves, because without
+-- 'owner', 'false' the output contains an ALTER TABLE ... OWNER TO line
+-- that varies across test runners.
+SELECT count(*) > 0 AS ok
+ FROM pg_get_table_ddl('basic'::regclass, VARIADIC NULL::text[]);
+
+-- include / exclude gating: each emits either only the listed kinds
+-- (include) or every kind except the listed ones (exclude). The two
+-- are mutually exclusive. Kind vocabulary: table, indexes,
+-- primary_key, unique, check, foreign_keys, exclusion, rules,
+-- statistics, triggers, policies, rls, replica_identity, partitions.
+-- NOT NULL is not in the vocabulary - always emitted to prevent
+-- producing schemas that silently accept NULLs the source would
+-- have rejected.
+--
+-- exclude=indexes hides the CREATE INDEX statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'exclude', 'indexes');
+
+-- exclude=primary_key,unique,check,foreign_keys,exclusion hides every
+-- table-level constraint. Inline CHECK in the CREATE TABLE body is
+-- suppressed too.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude',
+ 'primary_key,unique,check,foreign_keys,exclusion');
+
+-- exclude=foreign_keys suppresses only FOREIGN KEY constraints;
+-- PRIMARY KEY, UNIQUE, CHECK, and named NOT NULL stay. This is the
+-- first pass of the two-pass FK-clone workflow: emit tables without
+-- cross-table FKs, load data, then re-run with include=foreign_keys
+-- to add them once all targets exist.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude', 'foreign_keys');
+
+-- include=foreign_keys is the second pass of the two-pass FK-clone
+-- workflow: emit only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN
+-- KEY rows; the CREATE TABLE and every non-FK sub-object are
+-- suppressed (the table already exists from the first pass).
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys');
+
+-- A table with no foreign keys produces no rows under
+-- include=foreign_keys.
+SELECT * FROM pg_get_table_ddl('refd'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys');
+
+-- include=table emits only the bare CREATE TABLE (with any inline
+-- column-level NOT NULL and CHECK) - no indexes, no PRIMARY KEY ALTER,
+-- no other sub-objects. Useful for capturing a table's shape without
+-- pulling in everything attached to it.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'include', 'table');
+
+-- include and exclude are mutually exclusive.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'include', 'foreign_keys',
+ 'exclude', 'foreign_keys');
+
+-- Pub/sub schema clone: keep the table and its primary key, drop the
+-- rest of the constraints so the subscriber can replicate without
+-- pulling cross-table dependencies along.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'exclude',
+ 'unique,check,foreign_keys,exclusion');
+
+-- exclude=rules hides the CREATE RULE.
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'exclude', 'rules');
+
+-- exclude=statistics hides the CREATE STATISTICS.
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'exclude', 'statistics');
+
+-- exclude=rls hides the ENABLE/FORCE ROW LEVEL SECURITY toggles.
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'exclude', 'rls');
+
+-- exclude=replica_identity hides the REPLICA IDENTITY clause.
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'exclude', 'replica_identity');
+
+-- Partition children are emitted by default; exclude=partitions
+-- suppresses them so only the partitioned-table parent comes out.
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false',
+ 'exclude', 'partitions');
+
+-- Unknown kind name is rejected at option-parse time.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'exclude', 'no_such_kind');
+
+-- Whitespace around kind names is allowed; case is folded.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'exclude', ' INDEXES , RULES ');
+
+-- schema_qualified=false strips the schema from the target table itself
+-- and from same-schema sibling references (inheritance/partition
+-- parents, identity sequences in the same schema), while cross-schema
+-- references are kept qualified for correctness.
+--
+-- Bare CREATE TABLE for an ordinary table.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+
+-- INHERITS parent in the same schema is emitted unqualified.
+SELECT * FROM pg_get_table_ddl('ch'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+
+-- Partition parent in the same schema is emitted unqualified.
+SELECT * FROM pg_get_table_ddl('parted_range_1'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+
+-- Cross-schema FK target keeps qualification. The 'refd' table is in
+-- pgtbl_ddl_test; place an FK target in a different schema and verify it
+-- stays qualified even with schema_qualified=false.
+CREATE SCHEMA pgtbl_ddl_other;
+CREATE TABLE pgtbl_ddl_other.parent (id int PRIMARY KEY);
+CREATE TABLE xschema_fk (
+ id int PRIMARY KEY,
+ pid int REFERENCES pgtbl_ddl_other.parent
+);
+SELECT * FROM pg_get_table_ddl('xschema_fk'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+
+-- Identity column with a custom sequence name in the same schema is
+-- emitted unqualified.
+SELECT * FROM pg_get_table_ddl('id_custom'::regclass, 'owner', 'false',
+ 'schema_qualified', 'false');
+
+-- Round-trip: with schema_qualified=false, after SET search_path the
+-- DDL can be replayed into a different target schema.
+CREATE SCHEMA pgtbl_ddl_replay;
+DO $$
+DECLARE
+ stmt text;
+BEGIN
+ SET LOCAL search_path = pgtbl_ddl_replay;
+ FOR stmt IN
+ SELECT line FROM pg_get_table_ddl('pgtbl_ddl_test.basic'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false') AS line
+ LOOP
+ EXECUTE stmt;
+ END LOOP;
+END $$;
+SELECT relnamespace::regnamespace::text, relname
+FROM pg_class WHERE oid = 'pgtbl_ddl_replay.basic'::regclass;
+DROP SCHEMA pgtbl_ddl_replay CASCADE;
+DROP TABLE xschema_fk;
+DROP SCHEMA pgtbl_ddl_other CASCADE;
+
+-- Locally-declared CHECK on a partition child has no inline column
+-- list to live in (PARTITION OF form), so it must come out as a
+-- separate ALTER TABLE ... ADD CONSTRAINT statement.
+CREATE TABLE parted_chk (id int, val int) PARTITION BY RANGE (id);
+CREATE TABLE parted_chk_child PARTITION OF parted_chk
+ (CONSTRAINT chk_inline CHECK (val > 0))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('parted_chk_child'::regclass, 'owner', 'false');
+DROP TABLE parted_chk;
+
+-- Inheritance children of a parent with a generated column should not
+-- emit an ALTER COLUMN ... SET DEFAULT for the inherited generated
+-- column: the GENERATED expression carries down through inheritance,
+-- and SET DEFAULT would fail at replay against a generated column.
+CREATE TABLE par_gen (
+ id int,
+ g int GENERATED ALWAYS AS (id * 2) STORED
+);
+CREATE TABLE ch_gen () INHERITS (par_gen);
+SELECT * FROM pg_get_table_ddl('ch_gen'::regclass, 'owner', 'false');
+DROP TABLE par_gen CASCADE;
+
+-- A user-named NOT NULL on a local column is emitted inline as
+-- "CONSTRAINT name NOT NULL" so the original name is preserved. An
+-- auto-named NOT NULL on a local column is emitted as a plain inline
+-- "NOT NULL" - PG re-creates the auto-named constraint when CREATE
+-- TABLE runs, so the constraint loop skips both forms to avoid the
+-- redundant (and on table-rename or sequence-collision, broken)
+-- ALTER TABLE ... ADD CONSTRAINT ... NOT NULL statement.
+CREATE TABLE nn_named (
+ a int CONSTRAINT my_nn NOT NULL,
+ b int NOT NULL
+);
+SELECT * FROM pg_get_table_ddl('nn_named'::regclass, 'owner', 'false');
+DROP TABLE nn_named;
+
+-- Renaming the table after an IDENTITY NOT NULL column was declared
+-- leaves the auto-named constraint frozen at the original name, which
+-- no longer matches the post-rename "<table>_<col>_not_null" pattern.
+-- The emitted DDL must still round-trip: the constraint loop must not
+-- ALTER TABLE ... ADD CONSTRAINT a second NOT NULL, since CREATE TABLE
+-- with the inline IDENTITY already creates an auto-NOT-NULL under the
+-- new table name and PG only permits one NOT NULL per column.
+CREATE TABLE nn_renamed (
+ a int GENERATED ALWAYS AS IDENTITY NOT NULL
+);
+ALTER TABLE nn_renamed RENAME TO nn_renamed2;
+SELECT * FROM pg_get_table_ddl('nn_renamed2'::regclass, 'owner', 'false');
+DROP TABLE nn_renamed2;
+
+-- Locally-declared NOT NULL on an inherited column. The column itself
+-- is omitted from the child's column list (attislocal=false), so the
+-- user-named NOT NULL must come out via the post-CREATE constraint
+-- loop rather than inline.
+CREATE TABLE nn_par (a int);
+CREATE TABLE nn_ch (b int) INHERITS (nn_par);
+ALTER TABLE nn_ch ADD CONSTRAINT my_nn NOT NULL a;
+SELECT * FROM pg_get_table_ddl('nn_ch'::regclass, 'owner', 'false');
+DROP TABLE nn_par CASCADE;
+
+-- schema_qualified=false must drop the schema prefix from DEFAULT
+-- expressions and inline CHECK bodies that reference same-schema
+-- objects (sequence via nextval, function via direct call). This
+-- relies on the deparser respecting the narrowed search_path rather
+-- than on substring stripping.
+CREATE SCHEMA pgtbl_ddl_xref;
+CREATE SEQUENCE pgtbl_ddl_xref.myseq;
+CREATE FUNCTION pgtbl_ddl_xref.f(int) RETURNS int LANGUAGE sql IMMUTABLE
+ AS 'SELECT $1';
+CREATE TABLE pgtbl_ddl_xref.t (
+ id int DEFAULT nextval('pgtbl_ddl_xref.myseq'),
+ val int,
+ CONSTRAINT chk CHECK (pgtbl_ddl_xref.f(val) > 0)
+);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_xref.t'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+DROP SCHEMA pgtbl_ddl_xref CASCADE;
+
+-- schema_qualified=false must NOT corrupt string literals whose
+-- contents happen to contain the schema name followed by a dot. The
+-- in-string state of the prefix stripper preserves them verbatim.
+CREATE SCHEMA pgtbl_ddl_str;
+CREATE TABLE pgtbl_ddl_str.p (id int, note text) PARTITION BY RANGE (id);
+CREATE TABLE pgtbl_ddl_str.pc PARTITION OF pgtbl_ddl_str.p
+ (CONSTRAINT chk CHECK (note <> 'pgtbl_ddl_str.secret'))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_str.pc'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+DROP SCHEMA pgtbl_ddl_str CASCADE;
+
+-- schema_qualified=false must NOT corrupt double-quoted identifiers
+-- whose contents happen to contain the schema name followed by a dot
+-- (for example a column literally named "<schema>.weird" inside that
+-- schema). The in-ident state of the prefix stripper preserves them
+-- verbatim.
+CREATE SCHEMA pgtbl_ddl_qid;
+CREATE TABLE pgtbl_ddl_qid.p (id int, "pgtbl_ddl_qid.weird" int)
+ PARTITION BY RANGE (id);
+CREATE TABLE pgtbl_ddl_qid.pc PARTITION OF pgtbl_ddl_qid.p
+ (CONSTRAINT chk CHECK ("pgtbl_ddl_qid.weird" > 0))
+ FOR VALUES FROM (0) TO (100);
+SELECT * FROM pg_get_table_ddl('pgtbl_ddl_qid.pc'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+DROP SCHEMA pgtbl_ddl_qid CASCADE;
+
+-- schema_qualified=false when the base schema name itself requires
+-- quoting (its prefix starts with "). Both forms of the prefix -
+-- bare-lowercase and quoted - must be stripped from outer
+-- ALTER TABLE / ON / CREATE INDEX prefixes, while inner quoted
+-- identifiers that happen to begin the same way must be preserved.
+CREATE SCHEMA "PgTbl-Ddl-Q";
+CREATE TABLE "PgTbl-Ddl-Q"."T" (id int, val int,
+ CONSTRAINT chk CHECK (val > 0));
+CREATE INDEX "T_val_idx" ON "PgTbl-Ddl-Q"."T" (val);
+SELECT * FROM pg_get_table_ddl('"PgTbl-Ddl-Q"."T"'::regclass,
+ 'owner', 'false',
+ 'schema_qualified', 'false');
+DROP SCHEMA "PgTbl-Ddl-Q" CASCADE;
+
+-- Error: not an ordinary or partitioned table.
+CREATE VIEW v AS SELECT 1 AS x;
+SELECT * FROM pg_get_table_ddl('v'::regclass);
+
+CREATE SEQUENCE s;
+SELECT * FROM pg_get_table_ddl('s'::regclass);
+
+-- NULL argument returns no rows.
+SELECT * FROM pg_get_table_ddl(NULL);
+
+-- Unrecognized option.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'bogus', 'true');
+
+-- Odd number of variadic args.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'pretty');
+
+-- exclude=primary_key on a table whose REPLICA IDENTITY references
+-- the PK would emit a REPLICA IDENTITY USING INDEX clause that
+-- referenced an index the same DDL just dropped. Require the user
+-- to also exclude replica_identity in that case.
+CREATE TABLE ri_pk_excl (a int PRIMARY KEY);
+ALTER TABLE ri_pk_excl REPLICA IDENTITY USING INDEX ri_pk_excl_pkey;
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'exclude', 'primary_key');
+-- Same call but also excluding replica_identity succeeds.
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'exclude', 'primary_key,replica_identity');
+DROP TABLE ri_pk_excl;
+
+-- Round-trip verification. For every test table, capture the DDL the
+-- function emits, drop the schema, replay each table's DDL in
+-- dependency order, and confirm that pg_get_table_ddl on the recreated
+-- relation matches the original line-for-line. The final SELECT must
+-- return zero rows.
+CREATE TEMP TABLE pgtbl_ddl_rt_orig (name text, ord int, line text);
+INSERT INTO pgtbl_ddl_rt_orig
+SELECT t.name, o.ord, o.line
+FROM (VALUES
+ ('basic'), ('id_cols'), ('id_custom'), ('gen_cols'), ('storage_cols'),
+ ('refd'), ('cons'), ('idxd'),
+ ('par'), ('ch'), ('attopt'),
+ ('parted_range'), ('parted_range_1'), ('parted_range_def'),
+ ('parted_hash'), ('parted_hash_0'),
+ ('rt_log'), ('rt'),
+ ('stx'), ('rls'), ('ri_full'), ('ri_idx'), ('uno')
+ ) AS t(name),
+ LATERAL pg_get_table_ddl(('pgtbl_ddl_test.' || t.name)::regclass,
+ 'owner', 'false') WITH ORDINALITY o(line, ord);
+
+DO $$
+DECLARE
+ tables CONSTANT text[] := ARRAY[
+ 'basic', 'id_cols', 'id_custom', 'gen_cols', 'storage_cols',
+ 'refd', 'cons', 'idxd',
+ 'par', 'ch', 'attopt',
+ 'parted_range', 'parted_range_1', 'parted_range_def',
+ 'parted_hash', 'parted_hash_0',
+ 'rt_log', 'rt',
+ 'stx', 'rls', 'ri_full', 'ri_idx', 'uno'
+ ];
+ t text;
+ stmt text;
+BEGIN
+ DROP SCHEMA pgtbl_ddl_test CASCADE;
+ CREATE SCHEMA pgtbl_ddl_test;
+ FOREACH t IN ARRAY tables LOOP
+ FOR stmt IN
+ SELECT line FROM pgtbl_ddl_rt_orig WHERE name = t ORDER BY ord
+ LOOP
+ EXECUTE stmt;
+ END LOOP;
+ END LOOP;
+END $$;
+
+WITH after_ddl AS (
+ SELECT t.name, o.ord, o.line
+ FROM (SELECT DISTINCT name FROM pgtbl_ddl_rt_orig) AS t,
+ LATERAL pg_get_table_ddl(('pgtbl_ddl_test.' || t.name)::regclass,
+ 'owner', 'false') WITH ORDINALITY o(line, ord)
+)
+(SELECT 'missing-in-copy' AS kind, name, ord, line FROM pgtbl_ddl_rt_orig
+ EXCEPT
+ SELECT 'missing-in-copy', name, ord, line FROM after_ddl)
+UNION ALL
+(SELECT 'extra-in-copy' AS kind, name, ord, line FROM after_ddl
+ EXCEPT
+ SELECT 'extra-in-copy', name, ord, line FROM pgtbl_ddl_rt_orig)
+ORDER BY kind, name, ord;
+
+-- Cleanup.
+DROP SCHEMA pgtbl_ddl_test CASCADE;
--
2.51.0
view thread (54+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements
In-Reply-To: <CANxoLDdEBYzKfj3NGrP5PjoTa-eCViWPkFR-sXLq4bUnbokyPA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox