[PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement

Hello Hackers,

I am submitting a patch as part of the Retail DDL functions project
described here [1]. This patch creates a function called pg_get_type_ddl
designed to retrieve the DDL statement for CREATE TYPE. Users can get the
DDL by providing a TYPE name like the following for the ENUM type:

SELECT pg_get_type_ddl('type_name_enum');

which gives you:

pg_get_type_ddl
------------------------------------------------------------
CREATE TYPE public.type_name_enum AS ENUM ('one', 'two', 'three');

I split up the types range, enum, shell, composite, and a base type into
different functions which support `pg_get_type_ddl`. Otherwise, the
function seemed too long.

This patch includes documentation, comments, and regression tests, all of
which have run successfully.

1.
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net

--
Best,
Phil Alger

On 10/31/25 5:52 AM, Philip Alger wrote:
> Hello Hackers,
>
> I am submitting a patch as part of the Retail DDL functions project
> described here [1]. This patch creates a function called pg_get_type_ddl
> designed to retrieve the DDL statement for CREATE TYPE. Users can get
> the DDL by providing a TYPE name like the following for the ENUM type:
>
>    SELECT pg_get_type_ddl('type_name_enum');
>
Such functions are conventionally called xxxdef. For example:
pg_get_ruledef
pg_get_typedef
pg_get_indexdef

So I think its name should be called: pg_get_typedef

Furthermore, it would be even more beneficial if the parameter type
could support the Oid type (rather than merely supporting the text
type). This is also done to conform to the definition of similar functions.

--
Regards,
Quan Zongliang

> which gives you:
>
>                       pg_get_type_ddl
> ------------------------------------------------------------
>  CREATE TYPE public.type_name_enum AS ENUM ('one', 'two', 'three');
>
> I split up the types range, enum, shell, composite, and a base type into
> different functions which support `pg_get_type_ddl`. Otherwise, the
> function seemed too long.
>
> This patch includes documentation, comments, and regression tests, all
> of which have run successfully.
>
> 1. https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
> cb1e56f2e3e9%40dunslane.net <https://www.postgresql.org/message-
> id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net>
>
> --
> Best,
> Phil Alger

Hi Quan,

This is part of a larger project as noted here:

> I am submitting a patch as part of the Retail DDL functions project
> > described here [1].
>

> > 1. https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
> > cb1e56f2e3e9%40dunslane.net <https://www.postgresql.org/message-
> > id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net>
>

The idea here is to look up the created TYPE by its name and not the OID.
The function that is close is pg_get_viewdef(text) [1], but that's
deprecated.
Also, see threads for:

A. pg_get_trigger_ddl [2]
B. pg_get_tablespace_ddl [3]
C. pg_get_role_ddl [4]
D. pg_get_policy_ddl [5]
E. pg_get_domain_ddl [6]

1.https://www.postgresql.org/docs/18/functions-info.html
2.
https://www.postgresql.org/message-id/flat/CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com
3.
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s%3Db3Scsnj02C0kObQjnbL2ajfPWGEw%40mail.gmail.com
4.
https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471%40gmail.com
5.
https://www.postgresql.org/message-id/flat/CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com
6.
https://www.postgresql.org/message-id/flat/CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g%40mail.gmail.com#b1acf1f04ba8b36239fccdfae0110d3d

--
Best,
Phil Alger

On 10/31/25 9:34 AM, Philip Alger wrote:
> Hi Quan,
>
> This is part of a larger project as noted here:
>
Understood. This is an amazing job.

> > I am submitting a patch as part of the Retail DDL functions project
> > described here [1].
>
> > 1. https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
> <https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b->
> > cb1e56f2e3e9%40dunslane.net <http://40dunslane.net> <https://
> www.postgresql.org/message- <https://www.postgresql.org/message->
> > id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
> <http://40dunslane.net>>
>
>
> The idea here is to look up the created TYPE by its name and not the
> OID. The function that is close is pg_get_viewdef(text) [1], but that's
> deprecated.
>  Also, see threads for:
>
> A. pg_get_trigger_ddl [2]
> B. pg_get_tablespace_ddl [3]
> C. pg_get_role_ddl [4]
> D. pg_get_policy_ddl [5]
> E. pg_get_domain_ddl [6]
>
> 1.https://www.postgresql.org/docs/18/functions-info.html <https://
> www.postgresql.org/docs/18/functions-info.html>
> 2. https://www.postgresql.org/message-id/flat/
> CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com
> <https://www.postgresql.org/message-id/flat/
> CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com>
> 3. https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-
> s%3Db3Scsnj02C0kObQjnbL2ajfPWGEw%40mail.gmail.com <https://
> www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-
> s%3Db3Scsnj02C0kObQjnbL2ajfPWGEw%40mail.gmail.com>
> 4. https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-
> b943-9228b7da6471%40gmail.com <https://www.postgresql.org/message-id/
> flat/4c5f895e-3281-48f8-b943-9228b7da6471%40gmail.com>
> 5. https://www.postgresql.org/message-id/flat/
> CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com
> <https://www.postgresql.org/message-id/flat/
> CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com>
> 6. https://www.postgresql.org/message-id/flat/
> CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g%40mail.gmail.com#b1acf1f04ba8b36239fccdfae0110d3d <https://www.postgresql.org/message-id/flat/CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g%40mail.gmail.com#b1acf1f04ba8b36239fccdfae0110d3d>
>
> --
> Best,
> Phil Alger

On 10/31/25 5:52 AM, Philip Alger wrote:
> Hello Hackers,
>
> I am submitting a patch as part of the Retail DDL functions project
> described here [1]. This patch creates a function called pg_get_type_ddl
> designed to retrieve the DDL statement for CREATE TYPE. Users can get
> the DDL by providing a TYPE name like the following for the ENUM type:
>
>    SELECT pg_get_type_ddl('type_name_enum');
>
> which gives you:
>
>                       pg_get_type_ddl
> ------------------------------------------------------------
>  CREATE TYPE public.type_name_enum AS ENUM ('one', 'two', 'three');
>
> I split up the types range, enum, shell, composite, and a base type into
> different functions which support `pg_get_type_ddl`. Otherwise, the
> function seemed too long.
>
Found a small bug. MULTIRANGE_TYPE_NAME does not output schema.

Using the examples in the CREATE TYPE section.
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff =
float8mi);

At this point, it is normal.
SELECT pg_get_type_ddl('float8_range');
-----
CREATE TYPE public.float8_range AS RANGE (SUBTYPE = double precision,
SUBTYPE_OPCLASS = float8_ops, SUBTYPE_DIFF = float8mi,
MULTIRANGE_TYPE_NAME = float8_multirange);

If the schema of float8_multirange is changed.
ALTER TYPE float8_multirange SET SCHEMA p2;

The current output is incorrect:
MULTIRANGE_TYPE_NAME = float8_multirange

When outputting, the function "quote_qualified_identifier" should be
used instead of "quote_identifier".

Similarly, the function names in print_range_type_def and
print_base_type_def should also be processed in this way for their output.

--
Regards,
Quan Zongliang

> This patch includes documentation, comments, and regression tests, all
> of which have run successfully.
>
> 1. https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
> cb1e56f2e3e9%40dunslane.net <https://www.postgresql.org/message-
> id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net>
>
> --
> Best,
> Phil Alger

Hi Quan,

> Found a small bug. MULTIRANGE_TYPE_NAME does not output schema.
>
> When outputting, the function "quote_qualified_identifier" should be
> used instead of "quote_identifier".
>
> Similarly, the function names in print_range_type_def and
> print_base_type_def should also be processed in this way for their output.
>

I think I sorted it out. Attached is v2. Thanks for taking a look at it.

--
Best,
Phil Alger

> On Nov 1, 2025, at 06:29, Philip Alger <paalger0(at)gmail(dot)com> wrote:
>
>
> <v2-0001-Add-pg_get_type_ddl-function.patch>

1
```
+ /*
+ * Look up the type tuple to allow shell types.
+ */
+ typeTup = LookupTypeName(NULL, typeStruct, NULL, false);
+ if (typeTup == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("type \"%s\" does not exist",
+ TypeNameToString(typeStruct))));
```

Here when you call LookupTypeName(), you give the last parameter “missing_ok” a value of “false”, so that it would “ereport” inside LookupTypeName(), so your manual check of “if (typeTup == NULL)” will never be satisfied.

2
```
+{ oid => '8414', descr => 'get CREATE statement for type',
+ proname => 'pg_get_type_ddl', prorettype => 'text', proisstrict => 't',
+ proargtypes => 'text', proargnames => '{typname}',
+ prosrc => 'pg_get_type_ddl' },
```
Here you set proisstrict => ’t’. With strict mode, the function will not be executed if any of input arguments are NULL.

So add this test seems meaningless, because the function is not executed at all.
```
+SELECT pg_get_type_ddl(NULL);
+ pg_get_type_ddl
+-----------------
+
+(1 row)
```

3. As discussed in other get_xxx_ddl() patches, does this function needs a pretty flag? I think other patches have that.

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

Hi Chao,

Appreciate you pulling it apart:

> 1
> Here when you call LookupTypeName(), you give the last parameter
> “missing_ok” a value of “false”, so that it would “ereport” inside
> LookupTypeName(), so your manual check of “if (typeTup == NULL)” will never
> be satisfied.
>

Yeah, I changed that to `true`, so it should handle correctly now - v3. But
not sure how you got "it would “ereport” inside LookupTypeName()". I don't
see where ereport would happen inside `LookupTypeNameExtended`. It seems
like it would hit here and return NULL:

if (!OidIsValid(typoid))
{
if (typmod_p)
*typmod_p = -1;
return NULL;
}

> 2
> Here you set proisstrict => ’t’. With strict mode, the function will not
> be executed if any of input arguments are NULL.
>
> So add this test seems meaningless, because the function is not executed
> at all.
> ```
> +SELECT pg_get_type_ddl(NULL);
> + pg_get_type_ddl
> +-----------------
> +
> +(1 row)
>

I added that test to make sure it provides no output.

3. As discussed in other get_xxx_ddl() patches, does this function needs a
> pretty flag? I think other patches have that.
>

I see three functions that a couple people posted that use it; however, for
pg_get_policy_ddl is the only one using it to format the code. I am not
sure there is consensus on how SQL should be formatted.

--
Best,
Phil Alger