Thread
-
[PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Philip Alger <paalger0@gmail.com> — 2025-10-30T21:52:50Z
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 -
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Quan Zongliang <quanzongliang@yeah.net> — 2025-10-31T00:54:30Z
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 -
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Philip Alger <paalger0@gmail.com> — 2025-10-31T01:34:48Z
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
-
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Quan Zongliang <quanzongliang@yeah.net> — 2025-10-31T07:27:15Z
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
-
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Quan Zongliang <quanzongliang@yeah.net> — 2025-10-31T07:44:54Z
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 -
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Philip Alger <paalger0@gmail.com> — 2025-10-31T22:29:19Z
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
-
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Chao Li <li.evan.chao@gmail.com> — 2025-10-31T23:09:24Z
> 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/ -
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Philip Alger <paalger0@gmail.com> — 2025-11-01T17:44:47Z
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