v2-0001-refactor-pg_get_domain_ddl.no-cfbot
application/octet-stream
Filename: v2-0001-refactor-pg_get_domain_ddl.no-cfbot
Type: application/octet-stream
Part: 0
From 8a0cf326ca9e79af7a329cc584503f861ecb586e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 23 Oct 2025 12:10:40 +0800
Subject: [PATCH v2 1/1] refactor pg_get_domain_ddl
discussion: https://postgr.es/m/CAPgqM1V4LW2qiDLPsusb7s0kYbSDJjH5Tt+-ZzVmPU7xV0TJNQ@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 4 +-
src/backend/utils/adt/ruleutils.c | 217 +++++++++--------------
src/test/regress/expected/object_ddl.out | 98 +++++-----
3 files changed, 138 insertions(+), 181 deletions(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 9a937df960d..9c76a1e8986 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3827,8 +3827,8 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<indexterm>
<primary>pg_get_domain_ddl</primary>
</indexterm>
- <function>pg_get_domain_ddl</function> ( <parameter>domain</parameter> <type>text</type> )
- <returnvalue>regtype</returnvalue>
+ <function>pg_get_domain_ddl</function> ( <parameter>domain</parameter> <type>regtype</type> )
+ <returnvalue>text</returnvalue>
</para>
<para>
Reconstructs the creating command for a domain.
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b2d6935cbbc..b033680e7f8 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -13741,21 +13741,72 @@ get_range_partbound_string(List *bound_datums)
/*
- * Helper function to scan domain constraints
+ * pg_get_domain_ddl - Get CREATE DOMAIN statement for a domain
*/
-static void
-scan_domain_constraints(Oid domain_oid, List **validcons, List **invalidcons)
+Datum
+pg_get_domain_ddl(PG_FUNCTION_ARGS)
{
+ StringInfoData buf;
+ StringInfoData constr_buf;
+ Oid domain_oid = PG_GETARG_OID(0);
+ HeapTuple typeTuple;
+ HeapTuple baseTypeTuple;
+ Form_pg_type typForm;
+ Form_pg_type baseTypeForm;
+ Oid baseCollation = InvalidOid;
+ Node *defaultExpr;
Relation constraintRel;
SysScanDesc sscan;
ScanKeyData skey;
HeapTuple constraintTup;
- *validcons = NIL;
- *invalidcons = NIL;
+ /* Look up the domain in pg_type */
+ typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(domain_oid));
+ if (!HeapTupleIsValid(typeTuple))
+ PG_RETURN_NULL();
+
+ /* Build the DDL statement */
+ initStringInfo(&buf);
+ initStringInfo(&constr_buf);
+
+ typForm = (Form_pg_type) GETSTRUCT(typeTuple);
+
+ appendStringInfo(&buf, "CREATE DOMAIN %s AS %s",
+ generate_qualified_type_name(domain_oid),
+ generate_qualified_type_name(typForm->typbasetype));
+
+ /* 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));
+
+ Assert(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)
+ appendStringInfo(&buf, " COLLATE %s",
+ generate_collation_name(typForm->typcollation));
+ }
+
+ /* Get default expression */
+ defaultExpr = get_typdefault(domain_oid);
+
+ /* Add default value if present */
+ if (defaultExpr != NULL)
+ {
+ char *defaultValue = deparse_expression_pretty(defaultExpr, NIL,
+ false, false, 0, 0);
+
+ appendStringInfo(&buf, " DEFAULT %s", defaultValue);
+ }
constraintRel = table_open(ConstraintRelationId, AccessShareLock);
-
ScanKeyInit(&skey,
Anum_pg_constraint_contypid,
BTEqualStrategyNumber, F_OIDEQ,
@@ -13770,144 +13821,50 @@ scan_domain_constraints(Oid domain_oid, List **validcons, List **invalidcons)
while (HeapTupleIsValid(constraintTup = systable_getnext(sscan)))
{
+ char *constraintDef;
+
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(constraintTup);
if (con->convalidated)
- *validcons = lappend_oid(*validcons, con->oid);
+ {
+ constraintDef = pg_get_constraintdef_worker(con->oid, false,
+ PRETTYFLAG_PAREN,
+ true);
+ if (constraintDef)
+ appendStringInfo(&buf, " CONSTRAINT %s %s",
+ quote_identifier(NameStr(con->conname)),
+ constraintDef);
+ }
else
- *invalidcons = lappend_oid(*invalidcons, con->oid);
- }
+ {
+ constraintDef = pg_get_constraintdef_worker(con->oid, true,
+ PRETTYFLAG_PAREN,
+ true);
+ if (constraintDef)
+ {
+ appendStringInfoString(&constr_buf, constraintDef);
+ appendStringInfoChar(&constr_buf, ';');
+ appendStringInfoChar(&constr_buf, '\n');
+ }
+ }
+ }
systable_endscan(sscan);
table_close(constraintRel, AccessShareLock);
-}
-/*
- * Helper function to build CREATE DOMAIN statement
- */
-static void
-build_create_domain_statement(StringInfo buf, Form_pg_type typForm,
- Node *defaultExpr, List *validConstraints)
-{
- HeapTuple baseTypeTuple;
- Form_pg_type baseTypeForm;
- Oid baseCollation = InvalidOid;
+ appendStringInfoChar(&buf, ';');
- appendStringInfo(buf, "CREATE DOMAIN %s.%s AS %s",
- quote_identifier(get_namespace_name(typForm->typnamespace)),
- quote_identifier(NameStr(typForm->typname)),
- format_type_be(typForm->typbasetype));
-
- /* 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)
- {
- appendStringInfo(buf, " COLLATE %s",
- generate_collation_name(typForm->typcollation));
- }
- }
-
- /* Add default value if present */
- if (defaultExpr != NULL)
- {
- char *defaultValue = deparse_expression_pretty(defaultExpr, NIL, false, false, 0, 0);
- appendStringInfo(buf, " DEFAULT %s", defaultValue);
- }
-
- /* Add valid constraints */
- ListCell *lc;
- 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, PRETTYFLAG_PAREN, true);
-
- appendStringInfo(buf, " CONSTRAINT %s %s",
- quote_identifier(NameStr(con->conname)),
- 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)
-{
- ListCell *lc;
-
- foreach(lc, invalidConstraints)
+ if (constr_buf.len != 0)
{
- Oid constraintOid = lfirst_oid(lc);
- char *alterStmt = pg_get_constraintdef_worker(constraintOid, true, PRETTYFLAG_PAREN, true);
+ appendStringInfoChar(&buf, '\n');
+ appendStringInfoString(&buf, constr_buf.data);
- if (alterStmt)
- appendStringInfo(buf, "\n%s;", alterStmt);
+ /* truncate the last newline */
+ if (buf.len > 0 && buf.data[buf.len - 1] == '\n')
+ buf.data[--(buf.len)] = '\0';
}
-}
-
-/*
- * pg_get_domain_ddl - Get CREATE DOMAIN statement for a domain
- */
-Datum
-pg_get_domain_ddl(PG_FUNCTION_ARGS)
-{
- StringInfoData buf;
- Oid domain_oid = PG_GETARG_OID(0);
- 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))
- PG_RETURN_NULL();
-
- typForm = (Form_pg_type) GETSTRUCT(typeTuple);
-
- /* 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);
-
- /* Add ALTER DOMAIN statements for invalid constraints */
- if (list_length(invalidConstraints) > 0)
- add_alter_domain_statements(&buf, invalidConstraints);
/* Cleanup */
- list_free(validConstraints);
- list_free(invalidConstraints);
ReleaseSysCache(typeTuple);
PG_RETURN_TEXT_P(cstring_to_text(buf.data));
diff --git a/src/test/regress/expected/object_ddl.out b/src/test/regress/expected/object_ddl.out
index 3dc1f1bc049..8eb14cc5058 100644
--- a/src/test/regress/expected/object_ddl.out
+++ b/src/test/regress/expected/object_ddl.out
@@ -10,25 +10,25 @@ CREATE DOMAIN regress_us_postal_code AS TEXT
OR VALUE ~ '^\d{5}-\d{4}$'
);
SELECT pg_get_domain_ddl('regress_us_postal_code');
- pg_get_domain_ddl
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_us_postal_code AS text DEFAULT '00000'::text CONSTRAINT regress_us_postal_code_check CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text);
+ pg_get_domain_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_us_postal_code AS pg_catalog.text DEFAULT '00000'::text CONSTRAINT regress_us_postal_code_check CHECK (VALUE ~ '^\d{5}$'::text OR VALUE ~ '^\d{5}-\d{4}$'::text);
(1 row)
CREATE DOMAIN regress_domain_not_null AS INT NOT NULL;
SELECT pg_get_domain_ddl('regress_domain_not_null');
- pg_get_domain_ddl
----------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_domain_not_null AS integer CONSTRAINT regress_domain_not_null_not_null NOT NULL;
+ pg_get_domain_ddl
+-----------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_domain_not_null AS pg_catalog.int4 CONSTRAINT regress_domain_not_null_not_null NOT NULL;
(1 row)
CREATE DOMAIN regress_domain_check AS INT
CONSTRAINT regress_a CHECK (VALUE < 100)
CONSTRAINT regress_b CHECK (VALUE > 10);
SELECT pg_get_domain_ddl('regress_domain_check');
- pg_get_domain_ddl
-----------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_domain_check AS integer CONSTRAINT regress_a CHECK (VALUE < 100) CONSTRAINT regress_b CHECK (VALUE > 10);
+ pg_get_domain_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_domain_check AS pg_catalog.int4 CONSTRAINT regress_a CHECK (VALUE < 100) CONSTRAINT regress_b CHECK (VALUE > 10);
(1 row)
CREATE DOMAIN "regress_domain with space" AS INT
@@ -36,9 +36,9 @@ CREATE DOMAIN "regress_domain with space" AS INT
CONSTRAINT "regress_Constraint B" CHECK (VALUE > 10)
CONSTRAINT "regress_ConstraintC" CHECK (VALUE != 55);
SELECT pg_get_domain_ddl('"regress_domain with space"');
- pg_get_domain_ddl
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public."regress_domain with space" AS integer CONSTRAINT regress_a CHECK (VALUE < 100) CONSTRAINT "regress_Constraint B" CHECK (VALUE > 10) CONSTRAINT "regress_ConstraintC" CHECK (VALUE <> 55);
+ pg_get_domain_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public."regress_domain with space" AS pg_catalog.int4 CONSTRAINT regress_a CHECK (VALUE < 100) CONSTRAINT "regress_Constraint B" CHECK (VALUE > 10) CONSTRAINT "regress_ConstraintC" CHECK (VALUE <> 55);
(1 row)
-- Test error cases
@@ -55,60 +55,60 @@ SELECT pg_get_domain_ddl(NULL); -- should return NULL
-- Test domains with no constraints
CREATE DOMAIN regress_simple_domain AS text;
SELECT pg_get_domain_ddl('regress_simple_domain');
- pg_get_domain_ddl
------------------------------------------------------
- CREATE DOMAIN public.regress_simple_domain AS text;
+ pg_get_domain_ddl
+----------------------------------------------------------------
+ CREATE DOMAIN public.regress_simple_domain AS pg_catalog.text;
(1 row)
-- Test domain over another domain
CREATE DOMAIN regress_base_domain AS varchar(10);
CREATE DOMAIN regress_derived_domain AS regress_base_domain CHECK (LENGTH(VALUE) > 3);
SELECT pg_get_domain_ddl('regress_derived_domain');
- pg_get_domain_ddl
----------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_derived_domain AS regress_base_domain CONSTRAINT regress_derived_domain_check CHECK (length(VALUE::text) > 3);
+ pg_get_domain_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_derived_domain AS public.regress_base_domain CONSTRAINT regress_derived_domain_check CHECK (length(VALUE::text) > 3);
(1 row)
-- Test domain with complex default expressions
CREATE SEQUENCE regress_test_seq;
CREATE DOMAIN regress_seq_domain AS int DEFAULT nextval('regress_test_seq');
-SELECT pg_get_domain_ddl('regress_seq_domain');
- pg_get_domain_ddl
----------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_seq_domain AS integer DEFAULT nextval('regress_test_seq'::regclass);
-(1 row)
-
--- Test domain with a renamed sequence as default expression
-ALTER SEQUENCE regress_test_seq RENAME TO regress_test_seq_renamed;
SELECT pg_get_domain_ddl('regress_seq_domain');
pg_get_domain_ddl
-----------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_seq_domain AS integer DEFAULT nextval('regress_test_seq_renamed'::regclass);
+ CREATE DOMAIN public.regress_seq_domain AS pg_catalog.int4 DEFAULT nextval('regress_test_seq'::regclass);
+(1 row)
+
+-- Test domain with a renamed sequence as default expression
+ALTER SEQUENCE regress_test_seq RENAME TO regress_test_seq_renamed;
+SELECT pg_get_domain_ddl('regress_seq_domain');
+ pg_get_domain_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_seq_domain AS pg_catalog.int4 DEFAULT nextval('regress_test_seq_renamed'::regclass);
(1 row)
-- Test domain with type modifiers
CREATE DOMAIN regress_precise_numeric AS numeric(10,2) DEFAULT 0.00;
SELECT pg_get_domain_ddl('regress_precise_numeric');
- pg_get_domain_ddl
------------------------------------------------------------------------
- CREATE DOMAIN public.regress_precise_numeric AS numeric DEFAULT 0.00;
+ pg_get_domain_ddl
+------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_precise_numeric AS pg_catalog."numeric" DEFAULT 0.00;
(1 row)
-- Test domain over array type
CREATE DOMAIN regress_int_array_domain AS int[] CHECK (array_length(VALUE, 1) <= 5);
SELECT pg_get_domain_ddl('regress_int_array_domain');
- pg_get_domain_ddl
--------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_int_array_domain AS integer[] CONSTRAINT regress_int_array_domain_check CHECK (array_length(VALUE, 1) <= 5);
+ pg_get_domain_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_int_array_domain AS pg_catalog._int4 CONSTRAINT regress_int_array_domain_check CHECK (array_length(VALUE, 1) <= 5);
(1 row)
-- Test domain in non-public schema
CREATE SCHEMA regress_test_schema;
CREATE DOMAIN regress_test_schema.regress_schema_domain AS text DEFAULT 'test';
SELECT pg_get_domain_ddl('regress_test_schema.regress_schema_domain');
- pg_get_domain_ddl
----------------------------------------------------------------------------------------
- CREATE DOMAIN regress_test_schema.regress_schema_domain AS text DEFAULT 'test'::text;
+ pg_get_domain_ddl
+--------------------------------------------------------------------------------------------------
+ CREATE DOMAIN regress_test_schema.regress_schema_domain AS pg_catalog.text DEFAULT 'test'::text;
(1 row)
-- Test domain with multiple constraint types combined
@@ -118,18 +118,18 @@ CREATE DOMAIN regress_comprehensive_domain AS varchar(50)
CHECK (LENGTH(VALUE) >= 5)
CHECK (VALUE !~ '^\s*$'); -- not just whitespace
SELECT pg_get_domain_ddl('regress_comprehensive_domain');
- pg_get_domain_ddl
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_comprehensive_domain AS character varying DEFAULT 'default_value'::character varying CONSTRAINT regress_comprehensive_domain_not_null NOT NULL CONSTRAINT regress_comprehensive_domain_check CHECK (length(VALUE::text) >= 5) CONSTRAINT regress_comprehensive_domain_check1 CHECK (VALUE::text !~ '^\s*$'::text);
+ pg_get_domain_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_comprehensive_domain AS pg_catalog."varchar" DEFAULT 'default_value'::character varying CONSTRAINT regress_comprehensive_domain_not_null NOT NULL CONSTRAINT regress_comprehensive_domain_check CHECK (length(VALUE::text) >= 5) CONSTRAINT regress_comprehensive_domain_check1 CHECK (VALUE::text !~ '^\s*$'::text);
(1 row)
-- Test domain over composite type
CREATE TYPE regress_address_type AS (street text, city text, zipcode text);
CREATE DOMAIN regress_address_domain AS regress_address_type CHECK ((VALUE).zipcode ~ '^\d{5}$');
SELECT pg_get_domain_ddl('regress_address_domain');
- pg_get_domain_ddl
---------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_address_domain AS regress_address_type CONSTRAINT regress_address_domain_check CHECK ((VALUE).zipcode ~ '^\d{5}$'::text);
+ pg_get_domain_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_address_domain AS public.regress_address_type CONSTRAINT regress_address_domain_check CHECK ((VALUE).zipcode ~ '^\d{5}$'::text);
(1 row)
-- Test domain with NOT VALID constraint
@@ -138,7 +138,7 @@ ALTER DOMAIN regress_domain_not_valid ADD CONSTRAINT check_positive CHECK (VALUE
SELECT pg_get_domain_ddl('regress_domain_not_valid');
pg_get_domain_ddl
---------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_domain_not_valid AS integer; +
+ CREATE DOMAIN public.regress_domain_not_valid AS pg_catalog.int4; +
ALTER DOMAIN public.regress_domain_not_valid ADD CONSTRAINT check_positive CHECK (VALUE > 0) NOT VALID;
(1 row)
@@ -146,18 +146,18 @@ SELECT pg_get_domain_ddl('regress_domain_not_valid');
CREATE DOMAIN regress_domain_mixed AS int CHECK (VALUE != 0);
ALTER DOMAIN regress_domain_mixed ADD CONSTRAINT check_range CHECK (VALUE BETWEEN 1 AND 100) NOT VALID;
SELECT pg_get_domain_ddl('regress_domain_mixed');
- pg_get_domain_ddl
---------------------------------------------------------------------------------------------------------------------
- CREATE DOMAIN public.regress_domain_mixed AS integer CONSTRAINT regress_domain_mixed_check CHECK (VALUE <> 0); +
+ pg_get_domain_ddl
+------------------------------------------------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_domain_mixed AS pg_catalog.int4 CONSTRAINT regress_domain_mixed_check CHECK (VALUE <> 0);+
ALTER DOMAIN public.regress_domain_mixed ADD CONSTRAINT check_range CHECK (VALUE >= 1 AND VALUE <= 100) NOT VALID;
(1 row)
-- Test domain with collation
CREATE DOMAIN regress_domain_with_collate AS text COLLATE "C";
SELECT pg_get_domain_ddl('regress_domain_with_collate');
- pg_get_domain_ddl
------------------------------------------------------------------------
- CREATE DOMAIN public.regress_domain_with_collate AS text COLLATE "C";
+ pg_get_domain_ddl
+----------------------------------------------------------------------------------
+ CREATE DOMAIN public.regress_domain_with_collate AS pg_catalog.text COLLATE "C";
(1 row)
-- Cleanup
--
2.34.1