From 8a0cf326ca9e79af7a329cc584503f861ecb586e Mon Sep 17 00:00:00 2001 From: jian he 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} pg_get_domain_ddl - pg_get_domain_ddl ( domain text ) - regtype + pg_get_domain_ddl ( domain regtype ) + text 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