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