Re: [PATCH] pg_get_domain_ddl: DDL reconstruction function for CREATE DOMAIN statement

On Thu, Oct 16, 2025 at 5:17 PM Tim Waizenegger
<tim(dot)waizenegger(at)enterprisedb(dot)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";