v11-0001-Add-pg_get_domain_ddl-function-to-reconstruct-CR.patch

From f990fe27af72695580bbb0c9d284487385143202 Mon Sep 17 00:00:00 2001
From: Florin Irion <florin.irion@enterprisedb.com>
Date: Thu, 18 Sep 2025 18:52:43 +0200
Subject: [PATCH v11] 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

Reconstructs the CREATE DOMAIN statement for a given domain, including
base type, default, and constraints. NOT VALID constraints are rendered
as separate ALTER DOMAIN statements.

While this patch was in flight, master gained sibling functions
(pg_get_role_ddl(), pg_get_tablespace_ddl(), pg_get_database_ddl()) in
a new ddlutils.c with their own conventions. This version follows suit:
moved into ddlutils.c, converted to SETOF text (one row per statement),
and given named pg_proc.dat arguments instead of a SQL wrapper.

Also fixes two bugs found while aligning with the siblings: the DDL
round-trip test trigger silently dropped all but the first row of a
multi-row result, and an undefined domain OID returned NULL instead of
erroring.

Author: Florin Irion <florin.irion@enterprisedb.com>
Author: Tim Waizenegger <tim.waizenegger@enterprisedb.com>
Reviewed-by: Álvaro Herrera alvherre@alvh.no-ip.org
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Neil Chen <carpenter.nail.cz@gmail.com>
Reviewed-by: Man Zeng <zengman@halodbtech.com>
Reviewed-by: Haritabh <Gupta haritabh1992@gmail.com>
Reviewed-by: Japin Li <japinli@hotmail.com
Reference: PG-151
---
 doc/src/sgml/func/func-info.sgml              |  31 ++-
 src/backend/utils/adt/ddlutils.c              | 232 ++++++++++++++++++
 src/backend/utils/adt/ruleutils.c             |  29 ++-
 src/include/catalog/pg_proc.dat               |   6 +
 src/include/utils/ruleutils.h                 |   4 +
 .../expected/create_property_graph.out        |   1 +
 src/test/regress/expected/create_schema.out   |   2 +-
 src/test/regress/expected/domain.out          |  80 ++++++
 src/test/regress/expected/event_trigger.out   |  13 +-
 src/test/regress/expected/test_setup.out      |  73 ++++++
 src/test/regress/sql/domain.sql               |  39 +++
 src/test/regress/sql/test_setup.sql           |  75 ++++++
 12 files changed, 574 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 69ef3857cfa..672e681b3ab 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3853,7 +3853,7 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
    <para>
     The functions shown in <xref linkend="functions-get-object-ddl-table"/>
-    reconstruct DDL statements for various global database objects.
+    reconstruct DDL statements for various database objects.
     Each function returns a set of text rows, one SQL statement per row.
     (This is a decompiled reconstruction, not the original text of the
     command.)
@@ -3960,6 +3960,35 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         is false, the <literal>OWNER</literal> clause is omitted.
        </para></entry>
       </row>
+      <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>
+        <literal>DEFAULT</literal> false</optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <link linkend="sql-createdomain"><command>CREATE
+        DOMAIN</command></link> statement for the specified domain, followed
+        by <link linkend="sql-alterdomain"><command>ALTER DOMAIN ... ADD
+        CONSTRAINT ... NOT VALID</command></link> statements for any
+        constraints that have not been validated.  Each statement is
+        returned as a separate row.  When <parameter>pretty</parameter> is
+        true, the output is pretty-printed.
+       </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>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index a70f1c28655..0e35ceb7905 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -24,9 +24,11 @@
 #include "catalog/pg_auth_members.h"
 #include "catalog/pg_authid.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_tablespace.h"
+#include "catalog/pg_type.h"
 #include "commands/tablespace.h"
 #include "common/relpath.h"
 #include "funcapi.h"
@@ -56,6 +58,9 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
 static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid);
 static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
 										  bool no_owner, bool no_tablespace);
+static void scan_domain_constraints(Oid domain_oid, List **validcons,
+									List **invalidcons);
+static List *pg_get_domain_ddl_internal(Oid domain_oid, bool pretty);
 
 
 /*
@@ -975,3 +980,230 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
 		SRF_RETURN_DONE(funcctx);
 	}
 }
+
+/*
+ * scan_domain_constraints
+ *		Split a domain's pg_constraint rows into validated and NOT VALID
+ *		lists, each sorted by OID for stable, deterministic output.
+ */
+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);
+}
+
+/*
+ * pg_get_domain_ddl_internal
+ *		Generate DDL statements to recreate a domain.
+ *
+ * Returns a List of palloc'd strings, each a complete SQL statement.  The
+ * first element is always the CREATE DOMAIN statement, including any
+ * COLLATE/DEFAULT clauses and already-validated CHECK/NOT NULL constraints;
+ * subsequent elements, if any, are one ALTER DOMAIN ... ADD CONSTRAINT ...
+ * NOT VALID statement per constraint that has not yet been validated.
+ */
+static List *
+pg_get_domain_ddl_internal(Oid domain_oid, bool pretty)
+{
+	HeapTuple	typeTuple;
+	Form_pg_type typForm;
+	Node	   *defaultExpr;
+	List	   *validConstraints;
+	List	   *invalidConstraints;
+	StringInfoData buf;
+	List	   *statements = NIL;
+
+	typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(domain_oid));
+	if (!HeapTupleIsValid(typeTuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("domain with OID %u does not exist", domain_oid)));
+
+	typForm = (Form_pg_type) GETSTRUCT(typeTuple);
+
+	if (typForm->typtype != TYPTYPE_DOMAIN)
+	{
+		ReleaseSysCache(typeTuple);
+		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.")));
+	}
+
+	defaultExpr = get_typdefault(domain_oid);
+	scan_domain_constraints(domain_oid, &validConstraints, &invalidConstraints);
+
+	initStringInfo(&buf);
+
+	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));
+
+	/* only show COLLATE if it differs from the base type's collation */
+	if (OidIsValid(typForm->typcollation))
+	{
+		HeapTuple	baseTypeTuple = SearchSysCache1(TYPEOID,
+													ObjectIdGetDatum(typForm->typbasetype));
+		Oid			baseCollation = InvalidOid;
+
+		if (HeapTupleIsValid(baseTypeTuple))
+		{
+			baseCollation = ((Form_pg_type) GETSTRUCT(baseTypeTuple))->typcollation;
+			ReleaseSysCache(baseTypeTuple);
+		}
+
+		if (typForm->typcollation != baseCollation)
+			append_ddl_option(&buf, pretty, 4, "COLLATE %s",
+							  generate_collation_name(typForm->typcollation));
+	}
+
+	if (defaultExpr != NULL)
+	{
+		char	   *defaultValue = deparse_expression_for_ddl(defaultExpr, pretty);
+
+		append_ddl_option(&buf, pretty, 4, "DEFAULT %s", defaultValue);
+	}
+
+	foreach_oid(constraintOid, validConstraints)
+	{
+		HeapTuple	constraintTup;
+		Form_pg_constraint con;
+		char	   *constraintDef;
+
+		constraintTup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constraintOid));
+		if (!HeapTupleIsValid(constraintTup))
+			continue;			/* constraint was dropped concurrently */
+
+		con = (Form_pg_constraint) GETSTRUCT(constraintTup);
+		constraintDef = pg_get_constraintdef_for_ddl(constraintOid, false,
+													 pretty, true);
+
+		if (constraintDef == NULL)
+		{
+			ReleaseSysCache(constraintTup);
+			continue;			/* constraint was dropped concurrently */
+		}
+
+		append_ddl_option(&buf, pretty, 4, "CONSTRAINT %s",
+						  quote_identifier(NameStr(con->conname)));
+		append_ddl_option(&buf, pretty, 8, "%s", constraintDef);
+
+		ReleaseSysCache(constraintTup);
+	}
+
+	appendStringInfoChar(&buf, ';');
+	statements = lappend(statements, pstrdup(buf.data));
+
+	/* each NOT VALID constraint is its own ALTER DOMAIN row */
+	foreach_oid(constraintOid, invalidConstraints)
+	{
+		char	   *alterStmt = pg_get_constraintdef_for_ddl(constraintOid, true,
+															 pretty, true);
+
+		if (alterStmt == NULL)
+			continue;			/* constraint was dropped concurrently */
+
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "%s;", alterStmt);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	list_free(validConstraints);
+	list_free(invalidConstraints);
+	pfree(buf.data);
+	ReleaseSysCache(typeTuple);
+
+	return statements;
+}
+
+/*
+ * pg_get_domain_ddl
+ *		Return DDL to recreate a domain as a set of text rows.
+ *
+ * Each row is a complete SQL statement.  The first row is always the
+ * CREATE DOMAIN statement; subsequent rows, if any, are ALTER DOMAIN ...
+ * ADD CONSTRAINT ... NOT VALID statements for constraints that have not
+ * been validated.
+ */
+Datum
+pg_get_domain_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			domain_oid;
+		bool		pretty;
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		domain_oid = PG_GETARG_OID(0);
+		pretty = PG_GETARG_BOOL(1);
+
+		statements = pg_get_domain_ddl_internal(domain_oid, pretty);
+		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 = (char *) 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/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 88de5c0481c..6630bf48f54 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -99,6 +99,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
 
@@ -544,7 +549,6 @@ static char *generate_function_name(Oid funcid, int nargs,
 									bool inGroupBy);
 static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2);
 static void add_cast_to(StringInfo buf, Oid typid);
-static char *generate_qualified_type_name(Oid typid);
 static text *string_to_text(char *str);
 static char *flatten_reloptions(Oid relid);
 void		get_reloptions(StringInfo buf, Datum reloptions);
@@ -557,7 +561,6 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
-
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
 
@@ -14162,7 +14165,7 @@ add_cast_to(StringInfo buf, Oid typid)
  * SQL-standard type names ... although in current usage, this should
  * only get used for domains, so such cases wouldn't occur anyway.
  */
-static char *
+char *
 generate_qualified_type_name(Oid typid)
 {
 	HeapTuple	tp;
@@ -14364,3 +14367,23 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * Cross-file wrappers exposing this file's PRETTYFLAG_*-based deparsing to
+ * external callers that only know a plain "pretty" bool.
+ */
+char *
+pg_get_constraintdef_for_ddl(Oid constraintId, bool fullCommand,
+							 bool pretty, bool missing_ok)
+{
+	return pg_get_constraintdef_worker(constraintId, fullCommand,
+									   GET_DDL_PRETTY_FLAGS(pretty),
+									   missing_ok);
+}
+
+char *
+deparse_expression_for_ddl(Node *expr, bool pretty)
+{
+	return deparse_expression_pretty(expr, NIL, false, false,
+									 GET_DDL_PRETTY_FLAGS(pretty), 0);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3cb84359adf..9468f7eb830 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8634,6 +8634,12 @@
   proargtypes => 'regdatabase bool bool bool',
   proargnames => '{database,pretty,owner,tablespace}',
   proargdefaults => '{false,true,true}', prosrc => 'pg_get_database_ddl' },
+{ oid => '8024', descr => 'get DDL to recreate a domain',
+  proname => 'pg_get_domain_ddl', prorows => '10', proretset => 't',
+  provolatile => 's', pronargdefaults => '1', prorettype => 'text',
+  proargtypes => 'regtype bool',
+  proargnames => '{domain,pretty}', proargdefaults => '{false}',
+  prosrc => 'pg_get_domain_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/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 25c05e2f649..2bc0f3495c1 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -34,8 +34,11 @@ extern char *pg_get_partkeydef_columns(Oid relid, bool pretty);
 extern char *pg_get_partconstrdef_string(Oid partitionId, char *aliasname);
 
 extern char *pg_get_constraintdef_command(Oid constraintId);
+extern char *pg_get_constraintdef_for_ddl(Oid constraintId, bool fullCommand,
+										  bool pretty, bool missing_ok);
 extern char *deparse_expression(Node *expr, List *dpcontext,
 								bool forceprefix, bool showimplicit);
+extern char *deparse_expression_for_ddl(Node *expr, bool pretty);
 extern List *deparse_context_for(const char *aliasname, Oid relid);
 extern List *deparse_context_for_plan_tree(PlannedStmt *pstmt,
 										   List *rtable_names);
@@ -50,6 +53,7 @@ extern char *get_window_frame_options_for_explain(int frameOptions,
 												  bool forceprefix);
 extern char *generate_collation_name(Oid collid);
 extern char *generate_opclass_name(Oid opclass);
+extern char *generate_qualified_type_name(Oid typid);
 extern char *get_range_partbound_string(List *bound_datums);
 extern void get_reloptions(StringInfo buf, Datum reloptions);
 
diff --git a/src/test/regress/expected/create_property_graph.out b/src/test/regress/expected/create_property_graph.out
index 2f06c7ce5a8..77599f00331 100644
--- a/src/test/regress/expected/create_property_graph.out
+++ b/src/test/regress/expected/create_property_graph.out
@@ -220,6 +220,7 @@ ERROR:  mismatching number of properties in definition of label "t3l1"
 ALTER PROPERTY GRAPH g1 OWNER TO regress_graph_user1;
 SET ROLE regress_graph_user1;
 GRANT SELECT ON PROPERTY GRAPH g1 TO regress_graph_user2;
+WARNING:  regress_ddl_roundtrip_trigger_func: skipping DDL round-trip check (XX000): unsupported object type: 30
 GRANT UPDATE ON PROPERTY GRAPH g1 TO regress_graph_user2;  -- fail
 ERROR:  invalid privilege type UPDATE for property graph
 RESET ROLE;
diff --git a/src/test/regress/expected/create_schema.out b/src/test/regress/expected/create_schema.out
index b9ae4c402fd..f45845c3249 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -307,7 +307,6 @@ DROP SCHEMA regress_schema_misc CASCADE;
 NOTICE:  drop cascades to 17 other objects
 DETAIL:  drop cascades to function regress_schema_misc.cs_sum(integer)
 drop cascades to collation regress_schema_misc.cs_builtin_c
-drop cascades to type regress_schema_misc.cs_positive
 drop cascades to function regress_schema_misc.cs_add(integer,integer)
 drop cascades to operator regress_schema_misc.+(integer,integer)
 drop cascades to function regress_schema_misc.cs_proc(integer,integer)
@@ -322,5 +321,6 @@ drop cascades to type regress_schema_misc.cs_range
 drop cascades to function regress_schema_misc.cs_type_out(regress_schema_misc.cs_type)
 drop cascades to type regress_schema_misc.cs_type
 drop cascades to function regress_schema_misc.cs_type_in(cstring)
+drop cascades to type regress_schema_misc.cs_positive
 -- Clean up
 DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 62a48a523a2..0b169244827 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1378,6 +1378,86 @@ 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 constraints are always validated)
+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;
+(2 rows)
+
+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(999999::regtype);  -- should fail - undefined OID
+ERROR:  domain with OID 999999 does not exist
+SELECT pg_get_domain_ddl(NULL);  -- strict SRF: NULL argument yields no rows
+ pg_get_domain_ddl 
+-------------------
+(0 rows)
+
+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 86ae50ce531..e96fe6f45b7 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -815,12 +815,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..9aa9aea3cad 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -235,3 +235,76 @@ 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;
+
+    -- pg_event_trigger_ddl_commands() can raise before yielding a row (e.g.
+    -- an unsupported object type), so the whole loop -- not just each row --
+    -- must be inside the handler; a gap on one row of a multi-row event
+    -- therefore also discards verification already done for earlier rows in
+    -- that event. Accepted trade-off for this test convenience. A genuine
+    -- round-trip mismatch (SQLSTATE P0004) still propagates.
+    BEGIN
+        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);
+
+                    -- pg_get_<type>_ddl() is SETOF text; aggregate all rows
+                    -- in emission order instead of only capturing the first.
+                    EXECUTE format(
+                        'SELECT string_agg(ddl, E''\n'' ORDER BY ord)
+                           FROM pg_get_%s_ddl(%L) WITH ORDINALITY AS t(ddl, ord)',
+                        obj_type, r.object_identity)
+                        INTO original;
+                    EXECUTE format('DROP %s %s', obj_type, r.object_identity);
+                    EXECUTE original;
+                    EXECUTE format(
+                        'SELECT string_agg(ddl, E''\n'' ORDER BY ord)
+                           FROM pg_get_%s_ddl(%L) WITH ORDINALITY AS t(ddl, ord)',
+                        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;
+    EXCEPTION
+        WHEN OTHERS THEN
+            IF SQLSTATE = 'P0004' THEN
+                RAISE;
+            END IF;
+            RAISE WARNING 'regress_ddl_roundtrip_trigger_func: skipping DDL round-trip check (%): %',
+                SQLSTATE, SQLERRM;
+    END;
+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..89231828ef5 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -894,6 +894,45 @@ 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 constraints are always validated)
+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(999999::regtype);  -- should fail - undefined OID
+SELECT pg_get_domain_ddl(NULL);  -- strict SRF: NULL argument yields no rows
+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..741f1a0ce68 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -289,3 +289,78 @@ 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;
+
+    -- pg_event_trigger_ddl_commands() can raise before yielding a row (e.g.
+    -- an unsupported object type), so the whole loop -- not just each row --
+    -- must be inside the handler; a gap on one row of a multi-row event
+    -- therefore also discards verification already done for earlier rows in
+    -- that event. Accepted trade-off for this test convenience. A genuine
+    -- round-trip mismatch (SQLSTATE P0004) still propagates.
+    BEGIN
+        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);
+
+                    -- pg_get_<type>_ddl() is SETOF text; aggregate all rows
+                    -- in emission order instead of only capturing the first.
+                    EXECUTE format(
+                        'SELECT string_agg(ddl, E''\n'' ORDER BY ord)
+                           FROM pg_get_%s_ddl(%L) WITH ORDINALITY AS t(ddl, ord)',
+                        obj_type, r.object_identity)
+                        INTO original;
+                    EXECUTE format('DROP %s %s', obj_type, r.object_identity);
+                    EXECUTE original;
+                    EXECUTE format(
+                        'SELECT string_agg(ddl, E''\n'' ORDER BY ord)
+                           FROM pg_get_%s_ddl(%L) WITH ORDINALITY AS t(ddl, ord)',
+                        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;
+    EXCEPTION
+        WHEN OTHERS THEN
+            IF SQLSTATE = 'P0004' THEN
+                RAISE;
+            END IF;
+            RAISE WARNING 'regress_ddl_roundtrip_trigger_func: skipping DDL round-trip check (%): %',
+                SQLSTATE, SQLERRM;
+    END;
+END;
+$$;
+
+CREATE EVENT TRIGGER regress_ddl_roundtrip_trigger ON ddl_command_end
+    EXECUTE FUNCTION regress_ddl_roundtrip_trigger_func();
-- 
2.45.1