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

On Thu, Oct 16, 2025 at 1:05 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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().
> >
>
> 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)