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

From: Tim Waizenegger <tim(dot)waizenegger(at)enterprisedb(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PATCH] pg_get_domain_ddl: DDL reconstruction function for CREATE DOMAIN statement
Date: 2025-10-16 09:16:54
Message-ID: CAPgqM1V4LW2qiDLPsusb7s0kYbSDJjH5Tt+-ZzVmPU7xV0TJNQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Attachment Content-Type Size
v1-0001-Add-pg_get_domain_ddl-function-to-reconstruct-CRE.patch application/octet-stream 20.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mircea Cadariu 2025-10-16 09:27:25 Re: [BUG] temporary file usage report with extended protocol and unnamed portals
Previous Message jian he 2025-10-16 09:14:30 Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement