Обсуждение: [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');
------------------------------------------------------------
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.
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]
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@gmail.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 (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