Tom, Álvaro, thanks for the direction — I think the v9 patch addresses
both of your concerns nicely.
No extra test run. The event trigger is installed once in
test_setup.sql, so it piggybacks on the existing regression suite.
Every CREATE that fires ddl_command_end is checked inline —
there is no separate execution of the core tests.
Developers don't have to remember anything. The trigger inspects
command_tag to derive the object type, then probes pg_catalog for a
matching pg_get_<type>_ddl() function. If one exists it round-trips
the object right there; if not it silently moves on. Adding a new
pg_get_type_ddl() or pg_get_sequence_ddl() in the future
automatically extends coverage to every CREATE of that type across
the entire suite — zero changes to the trigger or to existing tests.
Inline verification at creation time. The trigger does:
get DDL → DROP → CREATE from DDL → get DDL → ASSERT match
Because it runs at creation time, nothing yet depends on the new object,
so the drop/recreate is safe. A session-local GUC guards against
recursion (the recreate fires the trigger again).
Bugs of omission. Tom raised the concern that comparing DDL output
against DDL output could mask a missing property. The key thing here is
that the test suite continues running with the recreated object. If
the DDL function omits, say, a CHECK constraint, the recreated domain
silently loses it — and any subsequent test that exercises that
constraint will fail. So omissions surface as unexpected failures
elsewhere in the suite, not just in the DDL comparison itself.
With the current patch, 160 domains across 33 test files are
automatically round-tripped. The dedicated object_ddl.sql file is
gone — a small set of pg_get_domain_ddl() output-format tests (pretty
printing, quoted identifiers, NOT VALID rendering, built-in type name
shadowing, error cases) now lives in domain.sql alongside the rest
of the domain coverage.
v9 attached.
While working on this I bumped into an unrelated crash and started a
new thread [1] for it.
[1]
https://www.postgresql.org/message-id/c6fff161-9aee-4290-9ada-71e21e4d84de%40gmail.com
--
Cheers,
Florin
EDB -- www.enterprisedb.com
From c65f5a1d7bc6893cf703e44db1f5d4267341dae2 Mon Sep 17 00:00:00 2001
From: Florin Irion <[email protected]>
Date: Thu, 18 Sep 2025 18:52:43 +0200
Subject: [PATCH v9] 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 69699f8830a..f4829cc7765 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -378,6 +378,13 @@ BEGIN ATOMIC
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';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
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 dac40992cbc..d1a4fbaf40a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8539,6 +8539,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 16e4530708c..8c7085e3b2c 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -785,12 +785,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
