v11-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch
application/octet-stream
Filename: v11-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v11-0001
Subject: Add pg_get_table_ddl() to reconstruct CREATE TABLE statements
| File | + | − |
|---|---|---|
| doc/src/sgml/func/func-info.sgml | 124 | 0 |
| src/backend/catalog/pg_inherits.c | 93 | 0 |
| src/backend/commands/tablecmds.c | 28 | 2 |
| src/backend/utils/adt/ddlutils.c | 2071 | 0 |
| src/include/catalog/pg_inherits.h | 1 | 0 |
| src/include/catalog/pg_proc.dat | 7 | 0 |
| src/include/commands/tablecmds.h | 3 | 0 |
| src/test/regress/expected/pg_get_table_ddl.out | 1222 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/sql/pg_get_table_ddl.sql | 717 | 0 |
From b291ba01311f35b89c0f2b860315612d7b74e77d Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Tue, 2 Jun 2026 14:18:40 +0530
Subject: [PATCH v11] 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 <akshay.joshi@enterprisedb.com>
Reviewed-by: Marcos Pegoraro <marcos@f10.com.br>
Reviewed-by: Zsolt Parragi <zsolt.parragi@percona.com>
Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 124 +
src/backend/catalog/pg_inherits.c | 93 +
src/backend/commands/tablecmds.c | 30 +-
src/backend/utils/adt/ddlutils.c | 2071 +++++++++++++++++
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 | 1222 ++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/pg_get_table_ddl.sql | 717 ++++++
10 files changed, 4267 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 34f4019690f..4edacf4bd87 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3947,6 +3947,130 @@ 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>only</literal> and <literal>except</literal>
+ options described below for filtering which object classes are
+ emitted.
+ </para>
+ <para>
+ The <literal>only</literal> and <literal>except</literal>
+ options each take a comma-separated list of object-class kind
+ names and are mutually exclusive (specifying both raises an
+ error). When <literal>only</literal> is set, only the
+ listed kinds are emitted; when <literal>except</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>index</literal>,
+ <literal>primary_key</literal>,
+ <literal>unique</literal>,
+ <literal>check</literal>,
+ <literal>foreign_key</literal>,
+ <literal>exclusion</literal>,
+ <literal>rule</literal>,
+ <literal>statistics</literal>,
+ <literal>trigger</literal>,
+ <literal>policy</literal>,
+ <literal>rls</literal> (the
+ <command>ENABLE</command>/<command>FORCE ROW LEVEL SECURITY</command>
+ toggles),
+ <literal>replica_identity</literal>, and
+ <literal>partition</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>only => 'foreign_key'</literal>, and the
+ pub/sub-style clone that keeps a primary key but drops every
+ other constraint can be written as
+ <literal>except => 'unique,check,foreign_key,exclusion'</literal>.
+ </para>
+ <para>
+ When the table has
+ <literal>REPLICA IDENTITY USING INDEX</literal> and the
+ <literal>replica_identity</literal> kind is in the active
+ filter, the kind that emits the referenced index
+ (<literal>primary_key</literal>, <literal>unique</literal>,
+ <literal>exclusion</literal>, or <literal>index</literal>) must
+ also be in the filter. Otherwise the emitted
+ <command>ALTER TABLE ... REPLICA IDENTITY USING INDEX</command>
+ would reference an index the same DDL never produced, and the
+ function reports an error before emitting any statements.
+ </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..742c8f1423a 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 only / except 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_INDEX,
+ TABLE_DDL_KIND_PRIMARY_KEY,
+ TABLE_DDL_KIND_UNIQUE,
+ TABLE_DDL_KIND_CHECK,
+ TABLE_DDL_KIND_FOREIGN_KEY,
+ TABLE_DDL_KIND_EXCLUSION,
+ TABLE_DDL_KIND_RULE,
+ TABLE_DDL_KIND_STATISTICS,
+ TABLE_DDL_KIND_TRIGGER,
+ TABLE_DDL_KIND_POLICY,
+ TABLE_DDL_KIND_RLS,
+ TABLE_DDL_KIND_REPLICA_IDENTITY,
+ TABLE_DDL_KIND_PARTITION,
+} TableDdlKind;
+
+static const struct
+{
+ const char *name;
+ TableDdlKind kind;
+} table_ddl_kind_names[] =
+{
+ {"table", TABLE_DDL_KIND_TABLE},
+ {"index", TABLE_DDL_KIND_INDEX},
+ {"primary_key", TABLE_DDL_KIND_PRIMARY_KEY},
+ {"unique", TABLE_DDL_KIND_UNIQUE},
+ {"check", TABLE_DDL_KIND_CHECK},
+ {"foreign_key", TABLE_DDL_KIND_FOREIGN_KEY},
+ {"exclusion", TABLE_DDL_KIND_EXCLUSION},
+ {"rule", TABLE_DDL_KIND_RULE},
+ {"statistics", TABLE_DDL_KIND_STATISTICS},
+ {"trigger", TABLE_DDL_KIND_TRIGGER},
+ {"policy", TABLE_DDL_KIND_POLICY},
+ {"rls", TABLE_DDL_KIND_RLS},
+ {"replica_identity", TABLE_DDL_KIND_REPLICA_IDENTITY},
+ {"partition", TABLE_DDL_KIND_PARTITION},
+};
+
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 only_kinds is non-NULL, only the
+ * kinds in that set are emitted; if except_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 *only_kinds;
+ Bitmapset *except_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,115 @@ 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++;
+ /*
+ * A comma must be followed by another entry. Without this
+ * check a trailing comma like "index," would be silently
+ * accepted as "index", while ",index" and "index,,rule" are
+ * already rejected as "empty entry" - this guard makes the
+ * three forms consistent.
+ */
+ if (*p == '\0')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("empty entry in option \"%s\"", optname)));
+ }
+
+ 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->only_kinds != NULL)
+ return bms_is_member((int) kind, ctx->only_kinds);
+ if (ctx->except_kinds != NULL)
+ return !bms_is_member((int) kind, ctx->except_kinds);
+ return true;
+}
+
/*
* Helper to append a formatted string with optional pretty-printing.
*/
@@ -1185,3 +1443,1816 @@ 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_INDEX))
+ 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 only / except
+ * vocabulary, so callers can produce e.g. an FK-only pass
+ * (only => 'foreign_key') or a pub/sub clone that keeps only
+ * the primary key (except => 'unique,check,foreign_key,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 only / except
+ * 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_kind_included(ctx, TABLE_DDL_KIND_CHECK))
+ continue;
+
+ /*
+ * For non-partition relations, CHECK is normally emitted
+ * inline in the CREATE TABLE body by
+ * append_inline_check_constraints, so we skip it here to
+ * avoid double emission. But when KIND_TABLE is not in
+ * the active filter (e.g. only=>'check' or a clone that
+ * targets only sub-objects), the inline pass never runs;
+ * fall through and emit each CHECK via ALTER TABLE so the
+ * user actually gets the constraint they asked for.
+ */
+ if (!is_partition &&
+ is_kind_included(ctx, TABLE_DDL_KIND_TABLE))
+ continue;
+ break;
+ case CONSTRAINT_FOREIGN:
+ if (!is_kind_included(ctx, TABLE_DDL_KIND_FOREIGN_KEY))
+ 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
+ * only=foreign_key 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_RULE) ||
+ 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_PARTITION) ||
+ 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 with one exception: KIND_PARTITION is a
+ * "gate" kind that controls whether we recurse at all, not a
+ * kind that the children themselves ever emit. If we propagated
+ * an only-set that contained PARTITION into a child, the child
+ * would not emit its own CREATE TABLE (KIND_TABLE absent) nor
+ * any sub-objects, and the recursion would produce nothing. So
+ * strip PARTITION out of only_kinds when recursing; if that
+ * empties the set, drop the filter entirely so the child emits
+ * its full DDL. except_kinds passes through unchanged because
+ * PARTITION in the except-set already stopped us from getting
+ * here.
+ */
+ 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.only_kinds = ctx->only_kinds;
+ childctx.except_kinds = ctx->except_kinds;
+
+ if (childctx.only_kinds != NULL &&
+ bms_is_member((int) TABLE_DDL_KIND_PARTITION,
+ childctx.only_kinds))
+ {
+ Bitmapset *child_only = bms_copy(childctx.only_kinds);
+
+ child_only = bms_del_member(child_only,
+ (int) TABLE_DDL_KIND_PARTITION);
+ if (bms_is_empty(child_only))
+ {
+ bms_free(child_only);
+ childctx.only_kinds = NULL;
+ }
+ else
+ childctx.only_kinds = child_only;
+ }
+
+ 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,
+ * only_kinds, except_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 only or except 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: if the table has REPLICA IDENTITY USING INDEX and the
+ * referenced index would not be emitted under the active filter, the
+ * emitted REPLICA IDENTITY clause would reference an index the same DDL
+ * never produced. Determine which kind would emit the index (one of
+ * primary_key / unique / exclusion for constraint-backed indexes,
+ * otherwise the generic "index" kind) and require it to be in scope
+ * whenever "replica_identity" is. The check uses is_kind_included so
+ * it covers both forms naturally: an "except" list that omits the
+ * source kind, and an "only" list that omits it.
+ */
+ if (rel->rd_rel->relreplident == REPLICA_IDENTITY_INDEX &&
+ is_kind_included(ctx, TABLE_DDL_KIND_REPLICA_IDENTITY))
+ {
+ Oid replidx = RelationGetReplicaIndex(rel);
+
+ if (OidIsValid(replidx))
+ {
+ TableDdlKind idx_kind = TABLE_DDL_KIND_INDEX;
+ Oid conoid = get_index_constraint(replidx);
+
+ if (OidIsValid(conoid))
+ {
+ HeapTuple conTup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(conoid));
+
+ if (HeapTupleIsValid(conTup))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+ switch (con->contype)
+ {
+ case CONSTRAINT_PRIMARY:
+ idx_kind = TABLE_DDL_KIND_PRIMARY_KEY;
+ break;
+ case CONSTRAINT_UNIQUE:
+ idx_kind = TABLE_DDL_KIND_UNIQUE;
+ break;
+ case CONSTRAINT_EXCLUSION:
+ idx_kind = TABLE_DDL_KIND_EXCLUSION;
+ break;
+ default:
+ break;
+ }
+ ReleaseSysCache(conTup);
+ }
+ }
+
+ if (!is_kind_included(ctx, idx_kind))
+ {
+ char *relname = pstrdup(RelationGetRelationName(rel));
+ const char *idx_name = table_ddl_kind_names[(int) idx_kind].name;
+
+ table_close(rel, AccessShareLock);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("REPLICA IDENTITY for table \"%s\" requires kind \"%s\" to be emitted",
+ relname, idx_name),
+ errdetail("The table's REPLICA IDENTITY USING INDEX references an index produced by the \"%s\" kind, which is not in the active filter.",
+ idx_name),
+ errhint("Either add \"%s\" to the filter or remove \"replica_identity\" from it.",
+ idx_name)));
+ }
+ }
+ }
+
+ /*
+ * 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. only => 'foreign_key' 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_TRIGGER))
+ {
+ 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_POLICY))
+ {
+ 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},
+ {"only", DDL_OPT_TEXT},
+ {"except", DDL_OPT_TEXT},
+ };
+ enum
+ {
+ OPT_PRETTY,
+ OPT_OWNER,
+ OPT_TABLESPACE,
+ OPT_SCHEMA_QUALIFIED,
+ OPT_ONLY,
+ OPT_EXCEPT,
+ };
+
+ 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_ONLY].isset && opts[OPT_EXCEPT].isset)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"only\" and \"except\" 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.only_kinds = NULL;
+ ctx.except_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_ONLY].isset)
+ ctx.only_kinds = parse_kind_list("only",
+ opts[OPT_ONLY].textval);
+ if (opts[OPT_EXCEPT].isset)
+ ctx.except_kinds = parse_kind_list("except",
+ opts[OPT_EXCEPT].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 fa76c7923f0..d1b87a764d4 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..b2b645094a5
--- /dev/null
+++ b/src/test/regress/expected/pg_get_table_ddl.out
@@ -0,0 +1,1222 @@
+--
+-- 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)
+
+-- only / except gating: each emits either only the listed kinds
+-- (only) or every kind except the listed ones (except). The two
+-- are mutually exclusive. Kind vocabulary: table, index,
+-- primary_key, unique, check, foreign_key, exclusion, rule,
+-- statistics, trigger, policy, rls, replica_identity, partition.
+-- NOT NULL is not in the vocabulary - always emitted to prevent
+-- producing schemas that silently accept NULLs the source would
+-- have rejected.
+--
+-- except=index hides the CREATE INDEX statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'index');
+ 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)
+
+-- except=primary_key,unique,check,foreign_key,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',
+ 'except',
+ 'primary_key,unique,check,foreign_key,exclusion');
+ pg_get_table_ddl
+---------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer);
+(1 row)
+
+-- except=foreign_key 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 only=foreign_key
+-- to add them once all targets exist.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'foreign_key');
+ 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)
+
+-- only=foreign_key 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',
+ 'only', 'foreign_key');
+ 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
+-- only=foreign_key.
+SELECT * FROM pg_get_table_ddl('refd'::regclass, 'owner', 'false',
+ 'only', 'foreign_key');
+ pg_get_table_ddl
+------------------
+(0 rows)
+
+-- only=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',
+ 'only', 'table');
+ pg_get_table_ddl
+--------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.idxd (id integer NOT NULL, name text);
+(1 row)
+
+-- only=check on a non-partition table. The constraint loop must emit
+-- column-level and table-level CHECK constraints via ALTER TABLE here,
+-- because the inline path in CREATE TABLE never runs (KIND_TABLE is
+-- not in the filter).
+CREATE TABLE chk_only (
+ id int PRIMARY KEY,
+ qty int CHECK (qty > 0),
+ CONSTRAINT chk_only_id_pos CHECK (id > 0)
+);
+SELECT * FROM pg_get_table_ddl('chk_only'::regclass, 'owner', 'false',
+ 'only', 'check');
+ pg_get_table_ddl
+------------------------------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.chk_only ADD CONSTRAINT chk_only_id_pos CHECK ((id > 0));
+ ALTER TABLE pgtbl_ddl_test.chk_only ADD CONSTRAINT chk_only_qty_check CHECK ((qty > 0));
+(2 rows)
+
+DROP TABLE chk_only;
+-- only=partition on a partitioned-table parent: each child's full DDL
+-- is emitted (CREATE TABLE ... PARTITION OF ...). The "partition"
+-- keyword is a gate at the parent level; it is stripped from the
+-- propagated filter so the child does not exclude its own
+-- CREATE TABLE.
+CREATE TABLE p_only (id int, val text) PARTITION BY RANGE (id);
+CREATE TABLE p_only_a PARTITION OF p_only FOR VALUES FROM (0) TO (100);
+CREATE TABLE p_only_b PARTITION OF p_only FOR VALUES FROM (100) TO (200);
+SELECT * FROM pg_get_table_ddl('p_only'::regclass, 'owner', 'false',
+ 'only', 'partition');
+ pg_get_table_ddl
+---------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.p_only_a PARTITION OF pgtbl_ddl_test.p_only FOR VALUES FROM (0) TO (100);
+ CREATE TABLE pgtbl_ddl_test.p_only_b PARTITION OF pgtbl_ddl_test.p_only FOR VALUES FROM (100) TO (200);
+(2 rows)
+
+DROP TABLE p_only;
+-- only and except are mutually exclusive.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'foreign_key',
+ 'except', 'foreign_key');
+ERROR: "only" and "except" 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',
+ 'except',
+ 'unique,check,foreign_key,exclusion');
+ pg_get_table_ddl
+---------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer);
+(1 row)
+
+-- except=rule hides the CREATE RULE.
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'except', 'rule');
+ pg_get_table_ddl
+----------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rt (id integer);
+(1 row)
+
+-- except=statistics hides the CREATE STATISTICS.
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'except', 'statistics');
+ pg_get_table_ddl
+--------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.stx (a integer, b integer, c integer);
+(1 row)
+
+-- except=rls hides the ENABLE/FORCE ROW LEVEL SECURITY toggles.
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'except', 'rls');
+ pg_get_table_ddl
+-----------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.rls (id integer);
+(1 row)
+
+-- except=replica_identity hides the REPLICA IDENTITY clause.
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'except', 'replica_identity');
+ pg_get_table_ddl
+--------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_full (a integer);
+(1 row)
+
+-- Partition children are emitted by default; except=partition
+-- suppresses them so only the partitioned-table parent comes out.
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false',
+ 'except', 'partition');
+ 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',
+ 'except', 'no_such_kind');
+ERROR: unrecognized kind "no_such_kind" in option "except"
+-- Whitespace around kind names is allowed; case is folded.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', ' INDEX , RULE ');
+ 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.
+-- REPLICA IDENTITY USING INDEX validation: if the referenced index
+-- would not be emitted (because its source kind is suppressed) but
+-- replica_identity itself would be, the function raises an error so
+-- the emitted DDL never dangles. Exercise all three constraint-backed
+-- forms (PK, UNIQUE, EXCLUSION) and a bare CREATE UNIQUE INDEX, in
+-- both the except-list and only-list shapes.
+--
+-- PK-backed: except=primary_key without except=replica_identity.
+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',
+ 'except', 'primary_key');
+ERROR: REPLICA IDENTITY for table "ri_pk_excl" requires kind "primary_key" to be emitted
+DETAIL: The table's REPLICA IDENTITY USING INDEX references an index produced by the "primary_key" kind, which is not in the active filter.
+HINT: Either add "primary_key" to the filter or remove "replica_identity" from it.
+-- Same call but also excluding replica_identity succeeds.
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'except', '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;
+-- UNIQUE-constraint-backed replica identity index.
+CREATE TABLE ri_uniq_excl (a int NOT NULL UNIQUE, b int);
+ALTER TABLE ri_uniq_excl REPLICA IDENTITY USING INDEX ri_uniq_excl_a_key;
+SELECT * FROM pg_get_table_ddl('ri_uniq_excl'::regclass, 'owner', 'false',
+ 'except', 'unique');
+ERROR: REPLICA IDENTITY for table "ri_uniq_excl" requires kind "unique" to be emitted
+DETAIL: The table's REPLICA IDENTITY USING INDEX references an index produced by the "unique" kind, which is not in the active filter.
+HINT: Either add "unique" to the filter or remove "replica_identity" from it.
+SELECT * FROM pg_get_table_ddl('ri_uniq_excl'::regclass, 'owner', 'false',
+ 'except', 'unique,replica_identity');
+ pg_get_table_ddl
+---------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_uniq_excl (a integer NOT NULL, b integer);
+(1 row)
+
+DROP TABLE ri_uniq_excl;
+-- Bare CREATE UNIQUE INDEX (no backing constraint) used as REPLICA
+-- IDENTITY: the index is emitted by the "index" kind, so excluding
+-- "index" without also excluding "replica_identity" must error.
+CREATE TABLE ri_idx_excl (a int NOT NULL, b int);
+CREATE UNIQUE INDEX ri_idx_excl_a_uniq ON ri_idx_excl (a);
+ALTER TABLE ri_idx_excl REPLICA IDENTITY USING INDEX ri_idx_excl_a_uniq;
+SELECT * FROM pg_get_table_ddl('ri_idx_excl'::regclass, 'owner', 'false',
+ 'except', 'index');
+ERROR: REPLICA IDENTITY for table "ri_idx_excl" requires kind "index" to be emitted
+DETAIL: The table's REPLICA IDENTITY USING INDEX references an index produced by the "index" kind, which is not in the active filter.
+HINT: Either add "index" to the filter or remove "replica_identity" from it.
+SELECT * FROM pg_get_table_ddl('ri_idx_excl'::regclass, 'owner', 'false',
+ 'except', 'index,replica_identity');
+ pg_get_table_ddl
+--------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.ri_idx_excl (a integer NOT NULL, b integer);
+(1 row)
+
+DROP TABLE ri_idx_excl;
+-- Include-side analog: only=replica_identity must include the kind
+-- that emits the index, or the function errors.
+CREATE TABLE ri_only (a int NOT NULL UNIQUE, b int);
+ALTER TABLE ri_only REPLICA IDENTITY USING INDEX ri_only_a_key;
+SELECT * FROM pg_get_table_ddl('ri_only'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+ERROR: REPLICA IDENTITY for table "ri_only" requires kind "unique" to be emitted
+DETAIL: The table's REPLICA IDENTITY USING INDEX references an index produced by the "unique" kind, which is not in the active filter.
+HINT: Either add "unique" to the filter or remove "replica_identity" from it.
+-- Including both kinds succeeds.
+SELECT * FROM pg_get_table_ddl('ri_only'::regclass, 'owner', 'false',
+ 'only', 'unique,replica_identity');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.ri_only ADD CONSTRAINT ri_only_a_key UNIQUE (a);
+ ALTER TABLE pgtbl_ddl_test.ri_only REPLICA IDENTITY USING INDEX ri_only_a_key;
+(2 rows)
+
+DROP TABLE ri_only;
+-- Per-kind only / except coverage. For each kind in the vocabulary,
+-- verify that only=K emits just that kind's output and that except=K
+-- omits just that kind's output. Kinds with no fixture above (trigger,
+-- policy) are still recognized but currently emit nothing; only=K
+-- returns zero rows for them. Fixtures used:
+-- idxd: PK + two secondary CREATE INDEX
+-- cons: PK + UNIQUE + CHECK + FK (constraint-backed)
+-- rt: CREATE RULE
+-- stx: CREATE STATISTICS
+-- rls: ENABLE/FORCE ROW LEVEL SECURITY
+-- ri_full: REPLICA IDENTITY FULL (no source-kind dependency)
+CREATE TABLE excl_fix (rng int4range,
+ EXCLUDE USING gist (rng WITH &&));
+-- only=K: emit only kind K's statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'index');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------
+ 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);
+(2 rows)
+
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'primary_key');
+ pg_get_table_ddl
+----------------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.idxd ADD CONSTRAINT idxd_pkey PRIMARY KEY (id);
+(1 row)
+
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'unique');
+ pg_get_table_ddl
+-----------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.cons ADD CONSTRAINT cons_b_key UNIQUE (b);
+(1 row)
+
+SELECT * FROM pg_get_table_ddl('excl_fix'::regclass, 'owner', 'false',
+ 'only', 'exclusion');
+ pg_get_table_ddl
+--------------------------------------------------------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.excl_fix ADD CONSTRAINT excl_fix_rng_excl EXCLUDE USING gist (rng WITH &&);
+(1 row)
+
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'only', 'rule');
+ pg_get_table_ddl
+----------------------------------------------------------------
+ CREATE RULE rt_log_insert AS +
+ ON INSERT TO pgtbl_ddl_test.rt DO INSERT INTO rt_log (id)+
+ VALUES (new.id);
+(1 row)
+
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'only', 'statistics');
+ pg_get_table_ddl
+------------------------------------------------------------------------
+ CREATE STATISTICS pgtbl_ddl_test.stx_ndv (ndistinct) ON a, b FROM stx;
+(1 row)
+
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'only', 'rls');
+ pg_get_table_ddl
+-----------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.rls ENABLE ROW LEVEL SECURITY;
+ ALTER TABLE pgtbl_ddl_test.rls FORCE ROW LEVEL SECURITY;
+(2 rows)
+
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+ pg_get_table_ddl
+-----------------------------------------------------------
+ ALTER TABLE pgtbl_ddl_test.ri_full REPLICA IDENTITY FULL;
+(1 row)
+
+-- trigger / policy are scaffolded but not yet emitting; only=K is a
+-- valid filter that produces zero rows. When the standalone
+-- pg_get_trigger_ddl / pg_get_policy_ddl helpers land, these queries
+-- start returning rows without an API change.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'trigger');
+ pg_get_table_ddl
+------------------
+(0 rows)
+
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'policy');
+ pg_get_table_ddl
+------------------
+(0 rows)
+
+-- except=K: emit everything except kind K's statements. PK/UNIQUE/
+-- INDEX are exercised on tables without REPLICA IDENTITY USING INDEX
+-- so the validation does not trip.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'primary_key');
+ 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);
+(3 rows)
+
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'unique');
+ 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_c_fkey FOREIGN KEY (c) REFERENCES refd(id) DEFERRABLE INITIALLY DEFERRED;
+(2 rows)
+
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'check');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.cons (a integer, b integer, c integer);
+ 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)
+
+SELECT * FROM pg_get_table_ddl('excl_fix'::regclass, 'owner', 'false',
+ 'except', 'exclusion');
+ pg_get_table_ddl
+-------------------------------------------------------
+ CREATE TABLE pgtbl_ddl_test.excl_fix (rng int4range);
+(1 row)
+
+-- except=table suppresses the CREATE TABLE + OWNER + child-default
+-- SET DEFAULT + attoptions passes, leaving only the sub-object passes.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'table');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------
+ 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);
+(3 rows)
+
+-- Multi-kind compositions.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'table,index');
+ 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);
+(3 rows)
+
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'primary_key,foreign_key');
+ 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)
+
+-- Duplicate entries in the list are silently de-duplicated by the
+-- Bitmapset.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'index,index');
+ pg_get_table_ddl
+-------------------------------------------------------------------------------------
+ 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);
+(2 rows)
+
+DROP TABLE excl_fix;
+-- only=K on a table that does not have any K of that kind is a valid
+-- filter and returns zero rows. Tested previously with foreign_key
+-- on 'refd'; here also rule, statistics, rls, replica_identity, and
+-- exclusion on 'basic' (which has only PK + columns).
+SELECT count(*) AS rule_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'rule');
+ rule_rows
+-----------
+ 0
+(1 row)
+
+SELECT count(*) AS stats_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'statistics');
+ stats_rows
+------------
+ 0
+(1 row)
+
+SELECT count(*) AS rls_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'rls');
+ rls_rows
+----------
+ 0
+(1 row)
+
+SELECT count(*) AS ri_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+ ri_rows
+---------
+ 0
+(1 row)
+
+SELECT count(*) AS excl_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'exclusion');
+ excl_rows
+-----------
+ 0
+(1 row)
+
+-- Input-validation edge cases for the comma-separated kind list. All
+-- of these raise an error at the variadic-parse step; the function
+-- never reaches relation open.
+-- Empty list:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', '');
+ERROR: option "only" must specify at least one kind
+-- Whitespace-only list:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', ' ');
+ERROR: empty entry in option "only"
+-- Trailing comma:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'index,');
+ERROR: empty entry in option "only"
+-- Leading comma:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', ',index');
+ERROR: empty entry in option "only"
+-- Consecutive commas:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'index,,rule');
+ERROR: empty entry in option "only"
+-- Token containing inner whitespace:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'foreign key');
+ERROR: invalid character in option "only"
+-- NULL value for a non-NULL option:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', NULL);
+ERROR: value for option "only" must not be null
+-- Option specified twice as separate name/value pairs:
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ 'only', 'index',
+ 'only', 'rule');
+ERROR: option "only" is specified more than once
+-- Same kind specified through both options is still mutex-rejected.
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ 'only', 'index',
+ 'except', 'rule');
+ERROR: "only" and "except" options are mutually exclusive
+-- 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..f6b73ff717b
--- /dev/null
+++ b/src/test/regress/sql/pg_get_table_ddl.sql
@@ -0,0 +1,717 @@
+--
+-- 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[]);
+
+-- only / except gating: each emits either only the listed kinds
+-- (only) or every kind except the listed ones (except). The two
+-- are mutually exclusive. Kind vocabulary: table, index,
+-- primary_key, unique, check, foreign_key, exclusion, rule,
+-- statistics, trigger, policy, rls, replica_identity, partition.
+-- NOT NULL is not in the vocabulary - always emitted to prevent
+-- producing schemas that silently accept NULLs the source would
+-- have rejected.
+--
+-- except=index hides the CREATE INDEX statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'index');
+
+-- except=primary_key,unique,check,foreign_key,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',
+ 'except',
+ 'primary_key,unique,check,foreign_key,exclusion');
+
+-- except=foreign_key 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 only=foreign_key
+-- to add them once all targets exist.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'foreign_key');
+
+-- only=foreign_key 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',
+ 'only', 'foreign_key');
+
+-- A table with no foreign keys produces no rows under
+-- only=foreign_key.
+SELECT * FROM pg_get_table_ddl('refd'::regclass, 'owner', 'false',
+ 'only', 'foreign_key');
+
+-- only=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',
+ 'only', 'table');
+
+-- only=check on a non-partition table. The constraint loop must emit
+-- column-level and table-level CHECK constraints via ALTER TABLE here,
+-- because the inline path in CREATE TABLE never runs (KIND_TABLE is
+-- not in the filter).
+CREATE TABLE chk_only (
+ id int PRIMARY KEY,
+ qty int CHECK (qty > 0),
+ CONSTRAINT chk_only_id_pos CHECK (id > 0)
+);
+SELECT * FROM pg_get_table_ddl('chk_only'::regclass, 'owner', 'false',
+ 'only', 'check');
+DROP TABLE chk_only;
+
+-- only=partition on a partitioned-table parent: each child's full DDL
+-- is emitted (CREATE TABLE ... PARTITION OF ...). The "partition"
+-- keyword is a gate at the parent level; it is stripped from the
+-- propagated filter so the child does not exclude its own
+-- CREATE TABLE.
+CREATE TABLE p_only (id int, val text) PARTITION BY RANGE (id);
+CREATE TABLE p_only_a PARTITION OF p_only FOR VALUES FROM (0) TO (100);
+CREATE TABLE p_only_b PARTITION OF p_only FOR VALUES FROM (100) TO (200);
+SELECT * FROM pg_get_table_ddl('p_only'::regclass, 'owner', 'false',
+ 'only', 'partition');
+DROP TABLE p_only;
+
+-- only and except are mutually exclusive.
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'foreign_key',
+ 'except', 'foreign_key');
+
+-- 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',
+ 'except',
+ 'unique,check,foreign_key,exclusion');
+
+-- except=rule hides the CREATE RULE.
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'except', 'rule');
+
+-- except=statistics hides the CREATE STATISTICS.
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'except', 'statistics');
+
+-- except=rls hides the ENABLE/FORCE ROW LEVEL SECURITY toggles.
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'except', 'rls');
+
+-- except=replica_identity hides the REPLICA IDENTITY clause.
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'except', 'replica_identity');
+
+-- Partition children are emitted by default; except=partition
+-- suppresses them so only the partitioned-table parent comes out.
+SELECT * FROM pg_get_table_ddl('parted_range'::regclass, 'owner', 'false',
+ 'except', 'partition');
+
+-- Unknown kind name is rejected at option-parse time.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'except', 'no_such_kind');
+
+-- Whitespace around kind names is allowed; case is folded.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', ' INDEX , RULE ');
+
+-- 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');
+
+-- REPLICA IDENTITY USING INDEX validation: if the referenced index
+-- would not be emitted (because its source kind is suppressed) but
+-- replica_identity itself would be, the function raises an error so
+-- the emitted DDL never dangles. Exercise all three constraint-backed
+-- forms (PK, UNIQUE, EXCLUSION) and a bare CREATE UNIQUE INDEX, in
+-- both the except-list and only-list shapes.
+--
+-- PK-backed: except=primary_key without except=replica_identity.
+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',
+ 'except', 'primary_key');
+-- Same call but also excluding replica_identity succeeds.
+SELECT * FROM pg_get_table_ddl('ri_pk_excl'::regclass, 'owner', 'false',
+ 'except', 'primary_key,replica_identity');
+DROP TABLE ri_pk_excl;
+
+-- UNIQUE-constraint-backed replica identity index.
+CREATE TABLE ri_uniq_excl (a int NOT NULL UNIQUE, b int);
+ALTER TABLE ri_uniq_excl REPLICA IDENTITY USING INDEX ri_uniq_excl_a_key;
+SELECT * FROM pg_get_table_ddl('ri_uniq_excl'::regclass, 'owner', 'false',
+ 'except', 'unique');
+SELECT * FROM pg_get_table_ddl('ri_uniq_excl'::regclass, 'owner', 'false',
+ 'except', 'unique,replica_identity');
+DROP TABLE ri_uniq_excl;
+
+-- Bare CREATE UNIQUE INDEX (no backing constraint) used as REPLICA
+-- IDENTITY: the index is emitted by the "index" kind, so excluding
+-- "index" without also excluding "replica_identity" must error.
+CREATE TABLE ri_idx_excl (a int NOT NULL, b int);
+CREATE UNIQUE INDEX ri_idx_excl_a_uniq ON ri_idx_excl (a);
+ALTER TABLE ri_idx_excl REPLICA IDENTITY USING INDEX ri_idx_excl_a_uniq;
+SELECT * FROM pg_get_table_ddl('ri_idx_excl'::regclass, 'owner', 'false',
+ 'except', 'index');
+SELECT * FROM pg_get_table_ddl('ri_idx_excl'::regclass, 'owner', 'false',
+ 'except', 'index,replica_identity');
+DROP TABLE ri_idx_excl;
+
+-- Include-side analog: only=replica_identity must include the kind
+-- that emits the index, or the function errors.
+CREATE TABLE ri_only (a int NOT NULL UNIQUE, b int);
+ALTER TABLE ri_only REPLICA IDENTITY USING INDEX ri_only_a_key;
+SELECT * FROM pg_get_table_ddl('ri_only'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+-- Including both kinds succeeds.
+SELECT * FROM pg_get_table_ddl('ri_only'::regclass, 'owner', 'false',
+ 'only', 'unique,replica_identity');
+DROP TABLE ri_only;
+
+-- Per-kind only / except coverage. For each kind in the vocabulary,
+-- verify that only=K emits just that kind's output and that except=K
+-- omits just that kind's output. Kinds with no fixture above (trigger,
+-- policy) are still recognized but currently emit nothing; only=K
+-- returns zero rows for them. Fixtures used:
+-- idxd: PK + two secondary CREATE INDEX
+-- cons: PK + UNIQUE + CHECK + FK (constraint-backed)
+-- rt: CREATE RULE
+-- stx: CREATE STATISTICS
+-- rls: ENABLE/FORCE ROW LEVEL SECURITY
+-- ri_full: REPLICA IDENTITY FULL (no source-kind dependency)
+CREATE TABLE excl_fix (rng int4range,
+ EXCLUDE USING gist (rng WITH &&));
+
+-- only=K: emit only kind K's statements.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'index');
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'primary_key');
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'unique');
+SELECT * FROM pg_get_table_ddl('excl_fix'::regclass, 'owner', 'false',
+ 'only', 'exclusion');
+SELECT * FROM pg_get_table_ddl('rt'::regclass, 'owner', 'false',
+ 'only', 'rule');
+SELECT * FROM pg_get_table_ddl('stx'::regclass, 'owner', 'false',
+ 'only', 'statistics');
+SELECT * FROM pg_get_table_ddl('rls'::regclass, 'owner', 'false',
+ 'only', 'rls');
+SELECT * FROM pg_get_table_ddl('ri_full'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+-- trigger / policy are scaffolded but not yet emitting; only=K is a
+-- valid filter that produces zero rows. When the standalone
+-- pg_get_trigger_ddl / pg_get_policy_ddl helpers land, these queries
+-- start returning rows without an API change.
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'trigger');
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'policy');
+
+-- except=K: emit everything except kind K's statements. PK/UNIQUE/
+-- INDEX are exercised on tables without REPLICA IDENTITY USING INDEX
+-- so the validation does not trip.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'primary_key');
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'unique');
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'except', 'check');
+SELECT * FROM pg_get_table_ddl('excl_fix'::regclass, 'owner', 'false',
+ 'except', 'exclusion');
+-- except=table suppresses the CREATE TABLE + OWNER + child-default
+-- SET DEFAULT + attoptions passes, leaving only the sub-object passes.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'except', 'table');
+
+-- Multi-kind compositions.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'table,index');
+SELECT * FROM pg_get_table_ddl('cons'::regclass, 'owner', 'false',
+ 'only', 'primary_key,foreign_key');
+-- Duplicate entries in the list are silently de-duplicated by the
+-- Bitmapset.
+SELECT * FROM pg_get_table_ddl('idxd'::regclass, 'owner', 'false',
+ 'only', 'index,index');
+
+DROP TABLE excl_fix;
+
+-- only=K on a table that does not have any K of that kind is a valid
+-- filter and returns zero rows. Tested previously with foreign_key
+-- on 'refd'; here also rule, statistics, rls, replica_identity, and
+-- exclusion on 'basic' (which has only PK + columns).
+SELECT count(*) AS rule_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'rule');
+SELECT count(*) AS stats_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'statistics');
+SELECT count(*) AS rls_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'rls');
+SELECT count(*) AS ri_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'replica_identity');
+SELECT count(*) AS excl_rows
+ FROM pg_get_table_ddl('basic'::regclass, 'owner', 'false',
+ 'only', 'exclusion');
+
+-- Input-validation edge cases for the comma-separated kind list. All
+-- of these raise an error at the variadic-parse step; the function
+-- never reaches relation open.
+-- Empty list:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', '');
+-- Whitespace-only list:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', ' ');
+-- Trailing comma:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'index,');
+-- Leading comma:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', ',index');
+-- Consecutive commas:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'index,,rule');
+-- Token containing inner whitespace:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', 'foreign key');
+-- NULL value for a non-NULL option:
+SELECT * FROM pg_get_table_ddl('basic'::regclass, 'only', NULL);
+-- Option specified twice as separate name/value pairs:
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ 'only', 'index',
+ 'only', 'rule');
+-- Same kind specified through both options is still mutex-rejected.
+SELECT * FROM pg_get_table_ddl('basic'::regclass,
+ 'only', 'index',
+ 'except', 'rule');
+
+-- 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