Обсуждение: [PATCH] pg_get_domain_ddl: DDL reconstruction function for CREATE DOMAIN statement

Hi all,

Following the recent "Retail DDL" discussion [1], we're submitting another
implementation: pg_get_domain_ddl().

This function reconstructs CREATE DOMAIN statements for existing domains,
following what seems to be the agreed pg_get_{objecttype}_ddl naming convention.

## Function

pg_get_domain_ddl(regtype) returns text

Returns a complete CREATE DOMAIN statement including base type, default values,
and all constraints. Uses get_typdefault() for proper expression handling and
supports schema-qualified domains.

## Example

```
CREATE DOMAIN regress_us_postal_code AS TEXT
    DEFAULT '00000'
    CONSTRAINT regress_us_postal_code_check
        CHECK (
            VALUE ~ '^\d{5}$'
    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);
(1 row)
```

## Implementation

- New "Get Object DDL Functions" documentation section
- Comprehensive regression tests in a separate file where we will add
  tests for the other objects functions.

We're unsure about the place where to add the trigger to the `object_ddl` test.
We added it now in `src/test/regress/parallel_schedule`, please let us know
if there is a better place.

This is part of a coordinated effort where we've divided the DDL functions
among different contributors. Additional patches for other object types
(tables, indexes, etc.) will follow from other team members.
Already submitted are: CREATE TRIGGER [2] and CREATE POLICY [3].

Patch attached. Feedback welcome.

[1] https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
[2] https://www.postgresql.org/message-id/flat/CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com
[3] https://www.postgresql.org/message-id/flat/CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com

---
Best regards,
Florin Irion
Tim Waizenegger

EDB (EnterpriseDB)

On Thu, Oct 16, 2025 at 5:17 PM Tim Waizenegger
<tim.waizenegger@enterprisedb.com> wrote:
>
> Hi all,
>
> Following the recent "Retail DDL" discussion [1], we're submitting another
> implementation: pg_get_domain_ddl().
>
> This function reconstructs CREATE DOMAIN statements for existing domains,
> following what seems to be the agreed pg_get_{objecttype}_ddl naming convention.
>
> ## Function
>
> pg_get_domain_ddl(regtype) returns text
>
> Returns a complete CREATE DOMAIN statement including base type, default values,
> and all constraints. Uses get_typdefault() for proper expression handling and
> supports schema-qualified domains.
>

        <indexterm>
+         <primary>pg_get_domain_ddl</primary>
+        </indexterm>
+        <function>pg_get_domain_ddl</function> (
<parameter>domain</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a domain.
+        The result is a complete <command>CREATE DOMAIN</command> statement.
+       </para></entry>

<type>text</type>

should be
<type>regtype</type>

+ Oid domain_oid = PG_GETARG_OID(0);
+ HeapTuple typeTuple;
,....
+
+ /* Look up the domain in pg_type */
+ typeTuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(domain_oid));
+

select pg_get_domain_ddl(-1);
will cause segfault.
see https://www.postgresql.org/message-id/3759807.1711658868%40sss.pgh.pa.us
and pg_get_trigger_ddl thread.


NOT VALID check constraint handling is tricky currently.
create domain x as int;
alter domain x add constraint cc check(value > 2) not valid;

select pg_get_domain_ddl('x'::regtype);
CREATE DOMAIN public.x AS integer CONSTRAINT cc CHECK (VALUE > 2) NOT VALID;
but putting the above to psql would result in syntax error.


https://www.postgresql.org/docs/current/sql-createdomain.html
[ COLLATE collation ]
part not handled?

create domain d0 as text collate "C";
select pg_get_domain_ddl('d0'::regtype);
        pg_get_domain_ddl
----------------------------------
 CREATE DOMAIN public.d0 AS text;
(1 row)

we should expect
CREATE DOMAIN public.d0 AS text COLLATE "C";

On Thu, Oct 16, 2025 at 1:05 PM jian he <jian.universality@gmail.com> wrote:
>
> On Thu, Oct 16, 2025 at 5:17 PM Tim Waizenegger
> <tim.waizenegger@enterprisedb.com> wrote:
> >
> > Hi all,
> >
> > Following the recent "Retail DDL" discussion [1], we're submitting another
> > implementation: pg_get_domain_ddl().
> >
>
> select pg_get_domain_ddl(-1);
> will cause segfault.
> see https://www.postgresql.org/message-id/3759807.1711658868%40sss.pgh.pa.us
> and pg_get_trigger_ddl thread.
>
>
> NOT VALID check constraint handling is tricky currently.
> create domain x as int;
> alter domain x add constraint cc check(value > 2) not valid;
>
> select pg_get_domain_ddl('x'::regtype);
> CREATE DOMAIN public.x AS integer CONSTRAINT cc CHECK (VALUE > 2) NOT VALID;
> but putting the above to psql would result in syntax error.
>
>
> https://www.postgresql.org/docs/current/sql-createdomain.html
> [ COLLATE collation ]
> part not handled?
>
> create domain d0 as text collate "C";
> select pg_get_domain_ddl('d0'::regtype);
>         pg_get_domain_ddl
> ----------------------------------
>  CREATE DOMAIN public.d0 AS text;
> (1 row)
>
> we should expect
> CREATE DOMAIN public.d0 AS text COLLATE "C";

Thanks for the feedback! We addressed the issues mentioned above and
also added more extensive test cases:

postgres=# select pg_get_domain_ddl(-1);
 pg_get_domain_ddl
-------------------

(1 row)

postgres=# create domain d0 as text collate "C";
CREATE DOMAIN
postgres=# select pg_get_domain_ddl('d0'::regtype);
              pg_get_domain_ddl
----------------------------------------------
 CREATE DOMAIN public.d0 AS text COLLATE "C";
(1 row)

postgres=# create domain x as int;
CREATE DOMAIN
postgres=# alter domain x add constraint cc check(value > 2) not valid;
ALTER DOMAIN
postgres=# select pg_get_domain_ddl('x'::regtype);
                          pg_get_domain_ddl
----------------------------------------------------------------------
 CREATE DOMAIN public.x AS integer;                                  +
 ALTER DOMAIN public.x ADD CONSTRAINT cc CHECK (VALUE > 2) NOT VALID;
(1 row)


updated patch is attached

---
Best regards,
Florin Irion
Tim Waizenegger

EDB (EnterpriseDB)

Hi Tim,

Thanks for working on this. I haven’t finished reviewing the entire patch. But I got a quick question:

> On Oct 22, 2025, at 17:32, Tim Waizenegger <tim.waizenegger@enterprisedb.com> wrote:
>
> updated patch is attached
>
> ---
> Best regards,
> Florin Irion
> Tim Waizenegger
>
> EDB (EnterpriseDB)
> <v1-0001-Add-pg_get_domain_ddl-function-to-reconstruct-CRE.patch>

```
+/*
+ * 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;
```

While reviewing a similar patch of pg_get_policy_ddl(), it take the last parameter as a pretty flag. I wonder why
pg_get_domain_ddl()doesn’t support an argument for pretty?  


See the code snippet from the other patch:

```
+/*
+ * pg_get_policy_ddl
+ *
+ * Generate a CREATE POLICY statement for the specified policy.
+ *
+ * tableID - Table ID of the policy.
+ * policyName - Name of the policy for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+    Oid            tableID = PG_GETARG_OID(0);
+    Name        policyName = PG_GETARG_NAME(1);
+    bool        pretty = PG_GETARG_BOOL(2);  # <====== This is the pretty arg
+    bool        attrIsNull;
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Wed, Oct 22, 2025 at 12:27 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> Hi Tim,
>
> Thanks for working on this. I haven’t finished reviewing the entire patch. But I got a quick question:
>
> While reviewing a similar patch of pg_get_policy_ddl(), it take the last parameter as a pretty flag. I wonder why
pg_get_domain_ddl()doesn’t support an argument for pretty? 
>
>

That's a good point; we'll add pretty printing support for consistency
with the other functions. I'll send a new patch in the coming days.

Best regards,
Florin Irion
Tim Waizenegger
EDB (EnterpriseDB)

On Wed, Oct 22, 2025 at 5:32 PM Tim Waizenegger
<tim.waizenegger@enterprisedb.com> wrote:
>
> updated patch is attached
>

I’ve done some refactoring, hope it’s now more intuitive to you.
Since a domain’s base type can itself be another domain, it’s better to use

    appendStringInfo(&buf, "CREATE DOMAIN %s AS %s",
                     generate_qualified_type_name(domain_oid),
                     generate_qualified_type_name(typForm->typbasetype));

then the domain's base type is also fully qualified.

I also refactored the logic for printing domain constraints, which should reduce
syscache lookups or table scans compared to your version.

please check the attached.