Hello,
v10 rebased on latest master attached.
Cheers,
Florin
www.enterprisedb.com
From 23da7528cf8d8112bb76e4037fecb53b4eb6bb67 Mon Sep 17 00:00:00 2001
From: Florin Irion <[email protected]>
Date: Thu, 18 Sep 2025 18:52:43 +0200
Subject: [PATCH v10] Add pg_get_domain_ddl() function to reconstruct CREATE
DOMAIN statements
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch introduces a new system function pg_get_domain_ddl() that
reconstructs the CREATE DOMAIN statement for a given domain. The function
takes a regtype parameter and returns the complete DDL statement including
the domain name, base type, default value, and all associated constraints.
The function follows the same pattern as other DDL reconstruction functions
like pg_get_functiondef() and pg_get_constraintdef(), providing a
decompiled reconstruction rather than the original command text.
Key features:
* Supports domains with default values
* Includes all domain constraints (CHECK, NOT NULL)
* NOT VALID constraints are handled with an extra ALTER command
* Properly quotes identifiers and schema names
* Handles complex constraint expressions
* Pretty printing support
* Warns against conflicting built-in names
* Uses GET_DDL_PRETTY_FLAGS macro for consistent pretty-printing behavior
A new documentation section "Get Object DDL Functions" has been created
to group DDL reconstruction functions, starting with pg_get_domain_ddl().
This provides a foundation for future DDL functions for other object types.
Regression tests for pg_get_domain_ddl() output are included in domain.sql,
covering pretty-printing, quoted identifiers, NOT VALID constraints, domain
shadowing built-in type names, and error cases.
A global event trigger installed in test_setup.sql automatically round-trips
every CREATE command whose object type has a matching pg_get_<type>_ddl()
function in pg_catalog. On each CREATE, the trigger extracts the DDL, drops
the object, recreates it from the DDL, and asserts the output is identical.
Because it runs inline at creation time, even objects that are later dropped
get verified. A session-local GUC guards against recursion. This gives
automatic round-trip coverage to every domain across the entire regression
suite — and extends to future pg_get_<type>_ddl() functions with zero
additional effort.
Reference: PG-151
Author: Florin Irion <[email protected]>
Author: Tim Waizenegger <[email protected]>
Reviewed-by: Álvaro Herrera [email protected]
Reviewed-by: jian he <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Neil Chen <[email protected]>
Reviewed-by: Man Zeng <[email protected]>
Reviewed-by: Haritabh <Gupta [email protected]>
---
doc/src/sgml/func/func-info.sgml | 53 ++++
src/backend/catalog/system_functions.sql | 7 +
src/backend/utils/adt/ruleutils.c | 260 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/domain.out | 78 ++++++
src/test/regress/expected/event_trigger.out | 13 +-
src/test/regress/expected/test_setup.out | 50 ++++
src/test/regress/sql/domain.sql | 37 +++
src/test/regress/sql/test_setup.sql | 52 ++++
9 files changed, 547 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 294f45e82a3..eb128fede09 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3845,4 +3845,57 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+ print the DDL statements for various database objects.
+ (This is a decompiled reconstruction, not the original text
+ of the command.)
+ </para>
+
+ <table id="functions-get-object-ddl-table">
+ <title>Get Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_domain_ddl</primary>
+ </indexterm>
+ <function>pg_get_domain_ddl</function> ( <parameter>domain</parameter>
<type>regtype</type>
+ <optional>, <parameter>pretty</parameter> <type>boolean</type>
</optional>)
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the creating command for a domain.
+ The result is a complete <command>CREATE DOMAIN</command> statement.
+ </para>
+ <para>
+ The <parameter>domain</parameter> parameter uses type
<type>regtype</type>,
+ which follows the standard <varname>search_path</varname> for type name
+ resolution. If a domain name conflicts with a built-in type name
+ (for example, a domain named <literal>int</literal>), you must use a
+ schema-qualified name (for example,
<literal>'public.int'::regtype</literal>)
+ to reference the domain.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql
b/src/backend/catalog/system_functions.sql
index 1c5b6d6df05..75838495991 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -372,3 +372,10 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
+
+CREATE OR REPLACE FUNCTION
+ pg_get_domain_ddl(domain_name regtype, pretty bool DEFAULT false)
+ RETURNS text
+ LANGUAGE internal
+ STABLE PARALLEL SAFE
+AS 'pg_get_domain_ddl_ext';
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index f16f1535785..45e04bcf15b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -94,6 +94,11 @@
((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
: PRETTYFLAG_INDENT)
+/* Conversion of "bool pretty" option for DDL statements (0 when false) */
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+ ((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+ : 0)
+
/* Default line length for pretty-print wrapping: 0 means wrap always */
#define WRAP_COLUMN_DEFAULT 0
@@ -547,6 +552,11 @@ static void get_json_table_nested_columns(TableFunc *tf,
JsonTablePlan *plan,
deparse_context *context,
bool showimplicit,
bool needcomma);
+static void get_formatted_string(StringInfo buf,
+ int
prettyFlags,
+ int
noOfTabChars,
+ const char
*fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_domain_ddl_worker(Oid domain_oid, int prettyFlags);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13760,3 +13770,253 @@ get_range_partbound_string(List *bound_datums)
return buf.data;
}
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - If pretty is true, the output includes tabs (\t) and newlines
(\n).
+ * noOfTabChars - indent with specified no of tabs.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int noOfTabChars, const
char *fmt,...)
+{
+ int save_errno = errno;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with tabs */
+ for (int i = 0; i < noOfTabChars; i++)
+ {
+ appendStringInfoChar(buf, '\t');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ for (;;)
+ {
+ va_list args;
+ int needed;
+
+ errno = save_errno;
+ va_start(args, fmt);
+ needed = appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+
+ if (needed == 0)
+ break;
+
+ enlargeStringInfo(buf, needed);
+ }
+}
+
+
+/*
+ * Helper function to scan domain constraints
+ */
+static void
+scan_domain_constraints(Oid domain_oid, List **validcons, List **invalidcons)
+{
+ Relation constraintRel;
+ SysScanDesc sscan;
+ ScanKeyData skey;
+ HeapTuple constraintTup;
+
+ *validcons = NIL;
+ *invalidcons = NIL;
+
+ constraintRel = table_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(domain_oid));
+
+ sscan = systable_beginscan(constraintRel,
+
ConstraintTypidIndexId,
+ true,
+ NULL,
+ 1,
+ &skey);
+
+ while (HeapTupleIsValid(constraintTup = systable_getnext(sscan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint)
GETSTRUCT(constraintTup);
+
+ if (con->convalidated)
+ *validcons = lappend_oid(*validcons, con->oid);
+ else
+ *invalidcons = lappend_oid(*invalidcons, con->oid);
+ }
+
+ systable_endscan(sscan);
+ table_close(constraintRel, AccessShareLock);
+
+ /* Sort constraints by OID for stable output */
+ if (list_length(*validcons) > 1)
+ list_sort(*validcons, list_oid_cmp);
+ if (list_length(*invalidcons) > 1)
+ list_sort(*invalidcons, list_oid_cmp);
+}
+
+/*
+ * Helper function to build CREATE DOMAIN statement
+ */
+static void
+build_create_domain_statement(StringInfo buf, Form_pg_type typForm,
+ Node *defaultExpr,
List *validConstraints, int prettyFlags)
+{
+ HeapTuple baseTypeTuple;
+ Form_pg_type baseTypeForm;
+ Oid baseCollation = InvalidOid;
+ ListCell *lc;
+
+ appendStringInfo(buf, "CREATE DOMAIN %s AS %s",
+
generate_qualified_type_name(typForm->oid),
+
format_type_extended(typForm->typbasetype,
+
typForm->typtypmod,
+
FORMAT_TYPE_TYPEMOD_GIVEN |
+
FORMAT_TYPE_FORCE_QUALIFY));
+
+ /* Add collation if it differs from base type's collation */
+ if (OidIsValid(typForm->typcollation))
+ {
+ /* Get base type's collation for comparison */
+ baseTypeTuple = SearchSysCache1(TYPEOID,
ObjectIdGetDatum(typForm->typbasetype));
+ if (HeapTupleIsValid(baseTypeTuple))
+ {
+ baseTypeForm = (Form_pg_type) GETSTRUCT(baseTypeTuple);
+ baseCollation = baseTypeForm->typcollation;
+ ReleaseSysCache(baseTypeTuple);
+ }
+
+ /* Only add COLLATE if domain's collation differs from base
type's */
+ if (typForm->typcollation != baseCollation)
+ {
+ get_formatted_string(buf, prettyFlags, 1, "COLLATE %s",
+
generate_collation_name(typForm->typcollation));
+ }
+ }
+
+ /* Add default value if present */
+ if (defaultExpr != NULL)
+ {
+ char *defaultValue =
deparse_expression_pretty(defaultExpr, NIL, false, false, prettyFlags, 0);
+
+ get_formatted_string(buf, prettyFlags, 1, "DEFAULT %s",
defaultValue);
+ }
+
+ /* Add valid constraints */
+ foreach(lc, validConstraints)
+ {
+ Oid constraintOid = lfirst_oid(lc);
+ HeapTuple constraintTup;
+ Form_pg_constraint con;
+ char *constraintDef;
+
+ /* Look up the constraint info */
+ constraintTup = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(constraintOid));
+ if (!HeapTupleIsValid(constraintTup))
+ continue; /* constraint was
dropped concurrently */
+
+ con = (Form_pg_constraint) GETSTRUCT(constraintTup);
+ constraintDef = pg_get_constraintdef_worker(constraintOid,
false, prettyFlags, true);
+
+ get_formatted_string(buf, prettyFlags, 1, "CONSTRAINT %s",
+
quote_identifier(NameStr(con->conname)));
+ get_formatted_string(buf, prettyFlags, 2, "%s", constraintDef);
+
+ ReleaseSysCache(constraintTup);
+ }
+
+ appendStringInfoChar(buf, ';');
+}
+
+/*
+ * Helper function to add ALTER DOMAIN statements for invalid constraints
+ */
+static void
+add_alter_domain_statements(StringInfo buf, List *invalidConstraints, int
prettyFlags)
+{
+ ListCell *lc;
+
+ foreach(lc, invalidConstraints)
+ {
+ Oid constraintOid = lfirst_oid(lc);
+ char *alterStmt =
pg_get_constraintdef_worker(constraintOid, true, prettyFlags, true);
+
+ if (alterStmt)
+ appendStringInfo(buf, "\n%s;", alterStmt);
+ }
+}
+
+/*
+ * pg_get_domain_ddl_ext - Get CREATE DOMAIN statement for a domain with
pretty-print option
+ */
+Datum
+pg_get_domain_ddl_ext(PG_FUNCTION_ARGS)
+{
+ Oid domain_oid = PG_GETARG_OID(0);
+ bool pretty = PG_GETARG_BOOL(1);
+ char *res;
+ int prettyFlags;
+
+ prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+
+ res = pg_get_domain_ddl_worker(domain_oid, prettyFlags);
+ if (res == NULL)
+ PG_RETURN_NULL();
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+
+
+static char *
+pg_get_domain_ddl_worker(Oid domain_oid, int prettyFlags)
+{
+ StringInfoData buf;
+ HeapTuple typeTuple;
+ Form_pg_type typForm;
+ Node *defaultExpr;
+ List *validConstraints;
+ List *invalidConstraints;
+
+ /* Look up the domain in pg_type */
+ typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(domain_oid));
+ if (!HeapTupleIsValid(typeTuple))
+ return NULL;
+
+ typForm = (Form_pg_type) GETSTRUCT(typeTuple);
+
+ /* Check that this is actually a domain */
+ if (typForm->typtype != TYPTYPE_DOMAIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a domain",
format_type_be(domain_oid)),
+ errhint("Use a schema-qualified name if the
domain name conflicts with a built-in name.")));
+
+ /* Get default expression */
+ defaultExpr = get_typdefault(domain_oid);
+
+ /* Scan for valid and invalid constraints */
+ scan_domain_constraints(domain_oid, &validConstraints,
&invalidConstraints);
+
+ /* Build the DDL statement */
+ initStringInfo(&buf);
+ build_create_domain_statement(&buf, typForm, defaultExpr,
validConstraints, prettyFlags);
+
+ /* Add ALTER DOMAIN statements for invalid constraints */
+ if (list_length(invalidConstraints) > 0)
+ add_alter_domain_statements(&buf, invalidConstraints,
prettyFlags);
+
+ /* Cleanup */
+ list_free(validConstraints);
+ list_free(invalidConstraints);
+ ReleaseSysCache(typeTuple);
+
+ return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..f2ad869b1b8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8585,6 +8585,9 @@
{ oid => '2508', descr => 'constraint description with pretty-print option',
proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' },
+{ oid => '8024', descr => 'get CREATE statement for DOMAIN with pretty option',
+ proname => 'pg_get_domain_ddl', provolatile => 's', prorettype => 'text',
+ proargtypes => 'regtype bool', prosrc => 'pg_get_domain_ddl_ext' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/domain.out
b/src/test/regress/expected/domain.out
index 62a48a523a2..6dd6408fd75 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1378,6 +1378,84 @@ LINE 1: ...m ADD CONSTRAINT the_constraint CHECK (value
> 0) NOT ENFORC...
^
DROP DOMAIN constraint_enforced_dom;
--
+-- pg_get_domain_ddl
+--
+-- Pretty output for a comprehensive domain (DEFAULT + NOT NULL + multiple
CHECKs)
+CREATE DOMAIN regress_ddl_comprehensive AS varchar(50)
+ NOT NULL
+ DEFAULT 'hello'
+ CHECK (LENGTH(VALUE) >= 3)
+ CHECK (VALUE !~ '^\s*$');
+SELECT pg_get_domain_ddl('regress_ddl_comprehensive', pretty => true);
+ pg_get_domain_ddl
+-------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_ddl_comprehensive AS character varying(50)+
+ DEFAULT 'hello'::character varying +
+ CONSTRAINT regress_ddl_comprehensive_not_null +
+ NOT NULL +
+ CONSTRAINT regress_ddl_comprehensive_check +
+ CHECK (length(VALUE::text) >= 3) +
+ CONSTRAINT regress_ddl_comprehensive_check1 +
+ CHECK (VALUE::text !~ '^\s*$'::text);
+(1 row)
+
+DROP DOMAIN regress_ddl_comprehensive;
+-- Quoted and special identifiers
+CREATE DOMAIN "regress_domain with space" AS int
+ CONSTRAINT "regress_Constraint A" CHECK (VALUE < 100)
+ CONSTRAINT "regress_Constraint B" CHECK (VALUE > 10);
+SELECT pg_get_domain_ddl('"regress_domain with space"', pretty => true);
+ pg_get_domain_ddl
+-------------------------------------------------------------
+ CREATE DOMAIN public."regress_domain with space" AS integer+
+ CONSTRAINT "regress_Constraint A" +
+ CHECK (VALUE < 100) +
+ CONSTRAINT "regress_Constraint B" +
+ CHECK (VALUE > 10);
+(1 row)
+
+DROP DOMAIN "regress_domain with space";
+-- NOT VALID constraint rendering (requires ALTER DOMAIN, not CREATE)
+CREATE DOMAIN regress_ddl_notvalid AS int;
+ALTER DOMAIN regress_ddl_notvalid ADD CONSTRAINT check_positive CHECK (VALUE >
0) NOT VALID;
+SELECT pg_get_domain_ddl('regress_ddl_notvalid', pretty => true);
+ pg_get_domain_ddl
+-----------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_ddl_notvalid AS integer;
+
+ ALTER DOMAIN public.regress_ddl_notvalid ADD CONSTRAINT check_positive CHECK
(VALUE > 0) NOT VALID;
+(1 row)
+
+DROP DOMAIN regress_ddl_notvalid;
+-- Domain shadowing a built-in type name
+CREATE DOMAIN public.int AS pg_catalog.int4;
+SELECT pg_get_domain_ddl('int'); -- should fail
+ERROR: "integer" is not a domain
+HINT: Use a schema-qualified name if the domain name conflicts with a
built-in name.
+SELECT pg_get_domain_ddl('public.int');
+ pg_get_domain_ddl
+----------------------------------------
+ CREATE DOMAIN public."int" AS integer;
+(1 row)
+
+DROP DOMAIN public.int;
+-- Error cases
+SELECT pg_get_domain_ddl('nonexistent_domain_type'::regtype); -- should fail
+ERROR: type "nonexistent_domain_type" does not exist
+LINE 1: SELECT pg_get_domain_ddl('nonexistent_domain_type'::regtype)...
+ ^
+SELECT pg_get_domain_ddl(NULL); -- should return NULL
+ pg_get_domain_ddl
+-------------------
+
+(1 row)
+
+SELECT pg_get_domain_ddl('pg_class'); -- should fail - not a domain
+ERROR: "pg_class" is not a domain
+HINT: Use a schema-qualified name if the domain name conflicts with a
built-in name.
+SELECT pg_get_domain_ddl('integer'); -- should fail - not a domain
+ERROR: "integer" is not a domain
+HINT: Use a schema-qualified name if the domain name conflicts with a
built-in name.
+--
-- Information schema
--
SELECT * FROM information_schema.column_domain_usage
diff --git a/src/test/regress/expected/event_trigger.out
b/src/test/regress/expected/event_trigger.out
index f897b079e67..7a0e5e3d35e 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -793,12 +793,13 @@ SELECT
LATERAL pg_identify_object_as_address('pg_event_trigger'::regclass, e.oid,
0) as b,
LATERAL pg_get_object_address(b.type, b.object_names, b.object_args) as a
ORDER BY e.evtname;
- evtname | descr | type |
object_names | object_args | ident
--------------------+---------------------------------+---------------+---------------------+-------------+--------------------------------------------------------
- end_rls_command | event trigger end_rls_command | event trigger |
{end_rls_command} | {} | ("event
trigger",,end_rls_command,end_rls_command)
- sql_drop_command | event trigger sql_drop_command | event trigger |
{sql_drop_command} | {} | ("event
trigger",,sql_drop_command,sql_drop_command)
- start_rls_command | event trigger start_rls_command | event trigger |
{start_rls_command} | {} | ("event
trigger",,start_rls_command,start_rls_command)
-(3 rows)
+ evtname | descr |
type | object_names | object_args |
ident
+-------------------------------+---------------------------------------------+---------------+---------------------------------+-------------+--------------------------------------------------------------------------------
+ end_rls_command | event trigger end_rls_command |
event trigger | {end_rls_command} | {} | ("event
trigger",,end_rls_command,end_rls_command)
+ regress_ddl_roundtrip_trigger | event trigger regress_ddl_roundtrip_trigger |
event trigger | {regress_ddl_roundtrip_trigger} | {} | ("event
trigger",,regress_ddl_roundtrip_trigger,regress_ddl_roundtrip_trigger)
+ sql_drop_command | event trigger sql_drop_command |
event trigger | {sql_drop_command} | {} | ("event
trigger",,sql_drop_command,sql_drop_command)
+ start_rls_command | event trigger start_rls_command |
event trigger | {start_rls_command} | {} | ("event
trigger",,start_rls_command,start_rls_command)
+(4 rows)
DROP EVENT TRIGGER start_rls_command;
DROP EVENT TRIGGER end_rls_command;
diff --git a/src/test/regress/expected/test_setup.out
b/src/test/regress/expected/test_setup.out
index 93a4c2691c1..0d58cbb628b 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -235,3 +235,53 @@ create function fipshash(text)
returns text
strict immutable parallel safe leakproof
return substr(encode(sha256($1::bytea), 'hex'), 1, 32);
+--
+-- DDL round-trip verification infrastructure.
+-- An event trigger that automatically verifies pg_get_<type>_ddl() for every
+-- CREATE command that has a matching reconstruction function. Runs inline at
+-- creation time so even objects that are later dropped get tested.
+--
+CREATE FUNCTION regress_ddl_roundtrip_trigger_func() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+DECLARE
+ r RECORD;
+ obj_type text;
+ original text;
+ recreated text;
+BEGIN
+ -- Recursion guard: the recreate step fires this trigger again.
+ IF current_setting('regress.ddl_roundtrip_in_progress', true) = 'true' THEN
+ RETURN;
+ END IF;
+
+ FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
+ LOOP
+ IF r.command_tag LIKE 'CREATE %' THEN
+ obj_type := lower(substring(r.command_tag from 'CREATE (.*)'));
+
+ IF EXISTS (
+ SELECT 1 FROM pg_proc
+ WHERE proname = format('pg_get_%s_ddl', obj_type)
+ AND pronamespace = 'pg_catalog'::regnamespace
+ ) THEN
+ PERFORM set_config('regress.ddl_roundtrip_in_progress',
'true', true);
+
+ EXECUTE format('SELECT pg_get_%s_ddl(%L)', obj_type,
r.object_identity)
+ INTO original;
+ EXECUTE format('DROP %s %s', obj_type, r.object_identity);
+ EXECUTE original;
+ EXECUTE format('SELECT pg_get_%s_ddl(%L)', obj_type,
r.object_identity)
+ INTO recreated;
+
+ ASSERT original = recreated,
+ format(E'DDL round-trip mismatch for %s %s:\n original:
%s\n recreated: %s',
+ obj_type, r.object_identity, original, recreated);
+
+ PERFORM set_config('regress.ddl_roundtrip_in_progress',
'false', true);
+ END IF;
+ END IF;
+ END LOOP;
+END;
+$$;
+CREATE EVENT TRIGGER regress_ddl_roundtrip_trigger ON ddl_command_end
+ EXECUTE FUNCTION regress_ddl_roundtrip_trigger_func();
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index b8f5a639712..dd0993a0c84 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -894,6 +894,43 @@ ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT
the_constraint CHECK (value
ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK
(value > 0) NOT ENFORCED;
DROP DOMAIN constraint_enforced_dom;
+--
+-- pg_get_domain_ddl
+--
+-- Pretty output for a comprehensive domain (DEFAULT + NOT NULL + multiple
CHECKs)
+CREATE DOMAIN regress_ddl_comprehensive AS varchar(50)
+ NOT NULL
+ DEFAULT 'hello'
+ CHECK (LENGTH(VALUE) >= 3)
+ CHECK (VALUE !~ '^\s*$');
+SELECT pg_get_domain_ddl('regress_ddl_comprehensive', pretty => true);
+DROP DOMAIN regress_ddl_comprehensive;
+
+-- Quoted and special identifiers
+CREATE DOMAIN "regress_domain with space" AS int
+ CONSTRAINT "regress_Constraint A" CHECK (VALUE < 100)
+ CONSTRAINT "regress_Constraint B" CHECK (VALUE > 10);
+SELECT pg_get_domain_ddl('"regress_domain with space"', pretty => true);
+DROP DOMAIN "regress_domain with space";
+
+-- NOT VALID constraint rendering (requires ALTER DOMAIN, not CREATE)
+CREATE DOMAIN regress_ddl_notvalid AS int;
+ALTER DOMAIN regress_ddl_notvalid ADD CONSTRAINT check_positive CHECK (VALUE >
0) NOT VALID;
+SELECT pg_get_domain_ddl('regress_ddl_notvalid', pretty => true);
+DROP DOMAIN regress_ddl_notvalid;
+
+-- Domain shadowing a built-in type name
+CREATE DOMAIN public.int AS pg_catalog.int4;
+SELECT pg_get_domain_ddl('int'); -- should fail
+SELECT pg_get_domain_ddl('public.int');
+DROP DOMAIN public.int;
+
+-- Error cases
+SELECT pg_get_domain_ddl('nonexistent_domain_type'::regtype); -- should fail
+SELECT pg_get_domain_ddl(NULL); -- should return NULL
+SELECT pg_get_domain_ddl('pg_class'); -- should fail - not a domain
+SELECT pg_get_domain_ddl('integer'); -- should fail - not a domain
+
--
-- Information schema
--
diff --git a/src/test/regress/sql/test_setup.sql
b/src/test/regress/sql/test_setup.sql
index 5854399a028..16bfc689348 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -289,3 +289,55 @@ create function fipshash(text)
returns text
strict immutable parallel safe leakproof
return substr(encode(sha256($1::bytea), 'hex'), 1, 32);
+
+--
+-- DDL round-trip verification infrastructure.
+-- An event trigger that automatically verifies pg_get_<type>_ddl() for every
+-- CREATE command that has a matching reconstruction function. Runs inline at
+-- creation time so even objects that are later dropped get tested.
+--
+CREATE FUNCTION regress_ddl_roundtrip_trigger_func() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+DECLARE
+ r RECORD;
+ obj_type text;
+ original text;
+ recreated text;
+BEGIN
+ -- Recursion guard: the recreate step fires this trigger again.
+ IF current_setting('regress.ddl_roundtrip_in_progress', true) = 'true' THEN
+ RETURN;
+ END IF;
+
+ FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
+ LOOP
+ IF r.command_tag LIKE 'CREATE %' THEN
+ obj_type := lower(substring(r.command_tag from 'CREATE (.*)'));
+
+ IF EXISTS (
+ SELECT 1 FROM pg_proc
+ WHERE proname = format('pg_get_%s_ddl', obj_type)
+ AND pronamespace = 'pg_catalog'::regnamespace
+ ) THEN
+ PERFORM set_config('regress.ddl_roundtrip_in_progress',
'true', true);
+
+ EXECUTE format('SELECT pg_get_%s_ddl(%L)', obj_type,
r.object_identity)
+ INTO original;
+ EXECUTE format('DROP %s %s', obj_type, r.object_identity);
+ EXECUTE original;
+ EXECUTE format('SELECT pg_get_%s_ddl(%L)', obj_type,
r.object_identity)
+ INTO recreated;
+
+ ASSERT original = recreated,
+ format(E'DDL round-trip mismatch for %s %s:\n original:
%s\n recreated: %s',
+ obj_type, r.object_identity, original, recreated);
+
+ PERFORM set_config('regress.ddl_roundtrip_in_progress',
'false', true);
+ END IF;
+ END IF;
+ END LOOP;
+END;
+$$;
+
+CREATE EVENT TRIGGER regress_ddl_roundtrip_trigger ON ddl_command_end
+ EXECUTE FUNCTION regress_ddl_roundtrip_trigger_func();
--
2.45.1
