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

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