[PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
Started by Philip Alger2 months ago8 messages
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.
/messages/by-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9@dunslane.net
--
Best,
Phil Alger
Attachments:
v1-0001-Add-pg_get_type_ddl-function.patchapplication/octet-stream; name=v1-0001-Add-pg_get_type_ddl-function.patchDownload
From 9a2b9ee0e5586315e29be5051ea354a9ad52bf5b Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Wed, 8 Oct 2025 19:29:02 -0500
Subject: [PATCH v1] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
This patch implements the pg_get_type_ddl() function, which emits
the DDL for CREATE TYPE. It includes functionality comments in
the code, as well as tests and documentation.
postgres=# SELECT pg_get_type_ddl('t_enum');
pg_get_type_ddl
------------------------------------------------------------
CREATE TYPE public.t_enum AS ENUM ('one', 'two', 'three');
(1 row)
Author: Phil Alger <paalger0@gmail.com>
PG-154
---
doc/src/sgml/func/func-info.sgml | 47 +++
src/backend/utils/adt/ruleutils.c | 392 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 +
src/test/regress/expected/create_type.out | 256 ++++++++++++++
src/test/regress/sql/create_type.sql | 164 +++++++++
5 files changed, 863 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94..8dbd379a07 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3795,6 +3795,53 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</tgroup>
</table>
+ </sect2>
+ <sect2 id="functions-info-retail">
+ <title>DDL Retail Functions</title>
+
+ <para>
+ The functions describes in <xref linkend="functions-info-retail-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions for each object type.
+ </para>
+
+ <table id="functions-info-retail-table">
+ <title>DDL Retail Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_type_ddl</primary>
+ </indexterm>
+ <function>pg_get_type_ddl</function>
+ ( <parameter>text</parameter> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the underlying <literal>CREATE TYPE</literal> DDL statement for A
+ given user defined type, returning the complete command as text. It generates
+ definitions for composite, enum, range, base, and shell types. An error is raised
+ if the function is provided with an unsupported type, such as a <literal>DOMAIN</literal>
+ since it has its own <literal>CREATE DOMAIN</literal> statement, a pseudo-type, or
+ a multirange type.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</sect2>
</sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..2d622502b1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -29,11 +29,13 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
+#include "catalog/pg_enum.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_range.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -52,6 +54,7 @@
#include "parser/parse_func.h"
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "parser/parser.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -373,6 +376,10 @@ static int print_function_arguments(StringInfo buf, HeapTuple proctup,
static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void print_function_trftypes(StringInfo buf, HeapTuple proctup);
static void print_function_sqlbody(StringInfo buf, HeapTuple proctup);
+static void print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid);
+static void print_enum_type_def(StringInfo buf, Oid typeid);
+static void print_range_type_def(StringInfo buf, Oid typeid);
+static void print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup);
static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces,
Bitmapset *rels_used);
static void set_deparse_for_query(deparse_namespace *dpns, Query *query,
@@ -2911,6 +2918,391 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+/*
+ * pg_get_type_ddl
+ * Returns the complete "CREATE TYPE ..." statement for the specified type.
+ */
+Datum
+pg_get_type_ddl(PG_FUNCTION_ARGS)
+{
+ text *typeArg = PG_GETARG_TEXT_PP(0);
+ List *names;
+ TypeName *typeStruct;
+ Oid typeid;
+ HeapTuple typeTup;
+ Form_pg_type typ;
+ char typeType;
+ char *nspName;
+ char *typeName;
+ const char *qualifiedTypeName;
+ StringInfoData buf;
+
+ /* Parse the type name using standard PostgreSQL identifier parsing */
+ names = textToQualifiedNameList(typeArg);
+ typeStruct = makeTypeNameFromNameList(names);
+
+ /*
+ * 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))));
+
+ typ = (Form_pg_type) GETSTRUCT(typeTup);
+ typeid = typ->oid;
+ typeType = typ->typtype;
+
+ /* Only support composite, enum, range, shell, and base types */
+ if (typeType == TYPTYPE_DOMAIN)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a domain type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_PSEUDO && typ->typisdefined)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a pseudo-type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_MULTIRANGE)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a multirange type",
+ format_type_be(typeid))));
+ }
+
+ initStringInfo(&buf);
+
+ /* Get the fully schema-qualified type name */
+ nspName = get_namespace_name(typ->typnamespace);
+ typeName = NameStr(typ->typname);
+ qualifiedTypeName = quote_qualified_identifier(nspName, typeName);
+
+ /* Start the CREATE TYPE command */
+ appendStringInfo(&buf, "CREATE TYPE %s", qualifiedTypeName);
+
+ /* Handle shell types (pseudo-types not yet defined) */
+ if (typeType == TYPTYPE_PSEUDO && !typ->typisdefined)
+ {
+ /* Just the CREATE TYPE statement with no definition */
+ appendStringInfo(&buf, ";");
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+ }
+
+ if (typeType == TYPTYPE_COMPOSITE)
+ print_composite_type_def(&buf, typ, typeid);
+ else if (typeType == TYPTYPE_ENUM)
+ print_enum_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_RANGE)
+ print_range_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_BASE)
+ print_base_type_def(&buf, typ, typeTup);
+ else
+ {
+ ReleaseSysCache(typeTup);
+ elog(ERROR, "unrecognized typtype: %d", (int) typeType);
+ }
+
+ appendStringInfo(&buf, ";");
+
+ /* Clean up */
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
+ * print_composite_type_def
+ * Append the definition of a composite type to buf.
+ */
+static void
+print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid)
+{
+ Relation rel;
+ TupleDesc tupdesc;
+ int i;
+ bool first = true;
+
+ if (!OidIsValid(typ->typrelid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("composite type \"%s\" has no associated relation",
+ format_type_be(typeid))));
+
+ rel = relation_open(typ->typrelid, AccessShareLock);
+ tupdesc = RelationGetDescr(rel);
+
+ appendStringInfoString(buf, " AS (");
+
+ /* Loop through each column of the composite type */
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ /* Add comma separator between columns */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ /* Output column name and type */
+ appendStringInfo(buf, "%s %s",
+ quote_identifier(NameStr(attr->attname)),
+ format_type_with_typemod(attr->atttypid,
+ attr->atttypmod));
+
+ /* Output COLLATE clause if there is a different collation than the default */
+ if (attr->attcollation != InvalidOid &&
+ attr->attcollation != get_typcollation(attr->atttypid))
+ appendStringInfo(buf, " COLLATE %s",
+ generate_collation_name(attr->attcollation));
+ }
+
+ appendStringInfoChar(buf, ')');
+ relation_close(rel, AccessShareLock);
+}
+
+/*
+ * print_enum_type_def
+ * Append the definition of an enum type to buf.
+ */
+static void
+print_enum_type_def(StringInfo buf, Oid typeid)
+{
+ Relation enumRel;
+ SysScanDesc enumScan;
+ HeapTuple enumTuple;
+ ScanKeyData skey;
+ bool first = true;
+
+ appendStringInfoString(buf, " AS ENUM (");
+
+ ScanKeyInit(&skey,
+ Anum_pg_enum_enumtypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(typeid));
+
+ enumRel = table_open(EnumRelationId, AccessShareLock);
+ enumScan = systable_beginscan(enumRel, EnumTypIdSortOrderIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(enumTuple = systable_getnext(enumScan)))
+ {
+ Form_pg_enum en = (Form_pg_enum) GETSTRUCT(enumTuple);
+
+ /* Add comma separator between enum values */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ appendStringInfo(buf, "%s",
+ quote_literal_cstr(NameStr(en->enumlabel)));
+ }
+
+ systable_endscan(enumScan);
+ table_close(enumRel, AccessShareLock);
+
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * print_range_type_def
+ * Append the definition of a range type to buf.
+ */
+static void
+print_range_type_def(StringInfo buf, Oid typeid)
+{
+ HeapTuple rngTup;
+ Form_pg_range rng;
+
+ rngTup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(typeid));
+ if (!HeapTupleIsValid(rngTup))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("range type \"%s\" has no pg_range entry",
+ format_type_be(typeid))));
+
+ rng = (Form_pg_range) GETSTRUCT(rngTup);
+
+ appendStringInfoString(buf, " AS RANGE (");
+ appendStringInfo(buf, "SUBTYPE = %s",
+ format_type_be(rng->rngsubtype));
+
+ /* Range type parameters */
+ if (OidIsValid(rng->rngsubopc))
+ {
+ appendStringInfoString(buf, ", SUBTYPE_OPCLASS =");
+ get_opclass_name(rng->rngsubopc, InvalidOid, buf);
+ }
+
+ if (OidIsValid(rng->rngcollation))
+ appendStringInfo(buf, ", COLLATION = %s",
+ generate_collation_name(rng->rngcollation));
+
+ if (OidIsValid(rng->rngcanonical))
+ appendStringInfo(buf, ", CANONICAL = %s",
+ quote_identifier(get_func_name(rng->rngcanonical)));
+
+ if (OidIsValid(rng->rngsubdiff))
+ appendStringInfo(buf, ", SUBTYPE_DIFF = %s",
+ quote_identifier(get_func_name(rng->rngsubdiff)));
+
+ if (OidIsValid(rng->rngmultitypid))
+ {
+ /* Look up and output the multirange type name */
+ HeapTuple multirangeTup;
+ Form_pg_type multirangeTyp;
+
+ multirangeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(rng->rngmultitypid));
+ if (!HeapTupleIsValid(multirangeTup))
+ elog(ERROR, "cache lookup failed for type %u", rng->rngmultitypid);
+
+ multirangeTyp = (Form_pg_type) GETSTRUCT(multirangeTup);
+ appendStringInfo(buf, ", MULTIRANGE_TYPE_NAME = %s",
+ quote_identifier(NameStr(multirangeTyp->typname)));
+
+ ReleaseSysCache(multirangeTup);
+ }
+
+ appendStringInfoChar(buf, ')');
+ ReleaseSysCache(rngTup);
+}
+
+/*
+ * print_base_type_def
+ * Append the definition of a base type to buf.
+ */
+static void
+print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup)
+{
+ Datum defaultDatum;
+ bool isnull;
+ char *defaultValue;
+
+ appendStringInfoString(buf, " (");
+ appendStringInfo(buf, "INPUT = %s, ",
+ quote_identifier(get_func_name(typ->typinput)));
+ appendStringInfo(buf, "OUTPUT = %s",
+ quote_identifier(get_func_name(typ->typoutput)));
+
+ /* Optional functions if defined */
+ if (OidIsValid(typ->typreceive))
+ appendStringInfo(buf, ", RECEIVE = %s",
+ quote_identifier(get_func_name(typ->typreceive)));
+
+ if (OidIsValid(typ->typsend))
+ appendStringInfo(buf, ", SEND = %s",
+ quote_identifier(get_func_name(typ->typsend)));
+
+ if (OidIsValid(typ->typmodin))
+ appendStringInfo(buf, ", TYPMOD_IN = %s",
+ quote_identifier(get_func_name(typ->typmodin)));
+
+ if (OidIsValid(typ->typmodout))
+ appendStringInfo(buf, ", TYPMOD_OUT = %s",
+ quote_identifier(get_func_name(typ->typmodout)));
+
+ if (OidIsValid(typ->typanalyze))
+ appendStringInfo(buf, ", ANALYZE = %s",
+ quote_identifier(get_func_name(typ->typanalyze)));
+
+ if (OidIsValid(typ->typsubscript))
+ appendStringInfo(buf, ", SUBSCRIPT = %s",
+ quote_identifier(get_func_name(typ->typsubscript)));
+
+ /* INTERNALLENGTH */
+ if (typ->typlen == -1)
+ appendStringInfoString(buf, ", INTERNALLENGTH = VARIABLE");
+ else
+ appendStringInfo(buf, ", INTERNALLENGTH = %d", typ->typlen);
+
+ /* PASSEDBYVALUE */
+ if (typ->typbyval)
+ appendStringInfoString(buf, ", PASSEDBYVALUE");
+
+ /* ALIGNMENT */
+ if (typ->typalign != TYPALIGN_INT)
+ {
+ switch (typ->typalign)
+ {
+ case TYPALIGN_CHAR:
+ appendStringInfoString(buf, ", ALIGNMENT = char");
+ break;
+ case TYPALIGN_SHORT:
+ appendStringInfoString(buf, ", ALIGNMENT = int2");
+ break;
+ case TYPALIGN_DOUBLE:
+ appendStringInfoString(buf, ", ALIGNMENT = double");
+ break;
+ }
+ }
+
+ /* STORAGE */
+ if (typ->typstorage != TYPSTORAGE_PLAIN)
+ {
+ switch (typ->typstorage)
+ {
+ /* Output non-default storage types */
+ case TYPSTORAGE_EXTERNAL:
+ appendStringInfoString(buf, ", STORAGE = external");
+ break;
+ case TYPSTORAGE_EXTENDED:
+ appendStringInfoString(buf, ", STORAGE = extended");
+ break;
+ case TYPSTORAGE_MAIN:
+ appendStringInfoString(buf, ", STORAGE = main");
+ break;
+ }
+ }
+
+ /* CATEGORY */
+ if (typ->typcategory != TYPCATEGORY_USER)
+ appendStringInfo(buf, ", CATEGORY = '%c'", typ->typcategory);
+
+ /* PREFERRED */
+ if (typ->typispreferred)
+ appendStringInfoString(buf, ", PREFERRED = true");
+
+ /* DEFAULT */
+ defaultDatum = SysCacheGetAttr(TYPEOID, typeTup,
+ Anum_pg_type_typdefault, &isnull);
+ if (!isnull)
+ {
+ defaultValue = TextDatumGetCString(defaultDatum);
+ appendStringInfo(buf, ", DEFAULT = %s",
+ quote_literal_cstr(defaultValue));
+ }
+
+ /* ELEMENT */
+ if (OidIsValid(typ->typelem))
+ appendStringInfo(buf, ", ELEMENT = %s",
+ format_type_be(typ->typelem));
+
+ /* DELIMITER */
+ if (typ->typdelim != ',')
+ appendStringInfo(buf, ", DELIMITER = '%c'", typ->typdelim);
+
+ /* COLLATABLE */
+ if (OidIsValid(typ->typcollation))
+ appendStringInfoString(buf, ", COLLATABLE = true");
+
+ appendStringInfoChar(buf, ')');
+}
+
/*
* pg_get_functiondef
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f7..34c76f191f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3999,6 +3999,10 @@
{ oid => '1665', descr => 'name of sequence for a serial column',
proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text',
proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' },
+{ 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' },
{ oid => '2098', descr => 'definition of a function',
proname => 'pg_get_functiondef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_functiondef' },
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 5181c4290b..00f97a4601 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -419,3 +419,259 @@ drop cascades to function myvarcharout(myvarchar)
drop cascades to function myvarcharsend(myvarchar)
drop cascades to function myvarcharrecv(internal,oid,integer)
drop cascades to type myvarchardom
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+ pg_get_type_ddl
+-----------------
+
+(1 row)
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+ERROR: type "i_dont_exist" does not exist
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+ pg_get_type_ddl
+-----------------------------
+ CREATE TYPE public.my_test;
+(1 row)
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+ERROR: type "void" is a pseudo-type
+SELECT pg_get_type_ddl('record');
+ERROR: type "record" is a pseudo-type
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+ pg_get_type_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.text (INPUT = textin, OUTPUT = textout, RECEIVE = textrecv, SEND = textsend, INTERNALLENGTH = VARIABLE, STORAGE = extended, CATEGORY = 'S', PREFERRED = true, COLLATABLE = true);
+(1 row)
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+ERROR: type "my_domain" is a domain type
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+ERROR: type "int4multirange" is a multirange type
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+----------------------------------------------------------------
+ CREATE TYPE public.test_enum AS ENUM ('red', 'green', 'blue');
+(1 row)
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------
+ CREATE TYPE public.test_address AS (street text, city text, state text, zip text);
+(1 row)
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+ pg_get_type_ddl
+-------------------------------------------------------------------------
+ CREATE TYPE public.test_composite_collate AS (field1 text COLLATE "C");
+(1 row)
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.int4range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, CANONICAL = int4range_canonical, SUBTYPE_DIFF = int4range_subdiff, MULTIRANGE_TYPE_NAME = int4multirange);
+(1 row)
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+ pg_get_type_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.my_custom_range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = my_custom_multirange);
+(1 row)
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+ pg_get_type_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_custom_multirange AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = my_custom_name);
+(1 row)
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_base_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_base_type is only a shell
+LINE 1: CREATE FUNCTION test_base_out(test_base_type) RETURNS cstrin...
+ ^
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+ pg_get_type_ddl
+----------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_base_type (INPUT = test_base_in, OUTPUT = test_base_out, INTERNALLENGTH = 4, PASSEDBYVALUE);
+(1 row)
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_complex_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_out(test_complex_type) RETURNS ...
+ ^
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: return type test_complex_type is only a shell
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_send(test_complex_type) RETURNS...
+ ^
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_complex_type (INPUT = test_complex_in, OUTPUT = test_complex_out, RECEIVE = test_complex_recv, SEND = test_complex_send, INTERNALLENGTH = 4, PASSEDBYVALUE, CATEGORY = 'N');
+(1 row)
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_storage_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_storage_type is only a shell
+LINE 1: CREATE FUNCTION test_storage_out(test_storage_type) RETURNS ...
+ ^
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+ pg_get_type_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_storage_type (INPUT = test_storage_in, OUTPUT = test_storage_out, INTERNALLENGTH = VARIABLE, STORAGE = extended);
+(1 row)
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_delim_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_delim_type is only a shell
+LINE 1: CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstr...
+ ^
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_delim_type (INPUT = test_delim_in, OUTPUT = test_delim_out, INTERNALLENGTH = 4, DELIMITER = ';');
+(1 row)
+
+-- Test types with different schemas
+CREATE SCHEMA test_schema;
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+-------------------------------------------------------------
+ CREATE TYPE test_schema.test_enum AS (f1 integer, f2 text);
+(1 row)
+
+RESET search_path;
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+ pg_get_type_ddl
+------------------------------------------------------------------
+ CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+(1 row)
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+ pg_get_type_ddl
+---------------------------------------------------------------
+ CREATE TYPE "TestSchema"."TestEnum" AS (f1 integer, f2 text);
+(1 row)
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_base_in(cstring)
+drop cascades to function test_base_out(test_base_type)
+DROP TYPE test_complex_type CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to function test_complex_in(cstring)
+drop cascades to function test_complex_out(test_complex_type)
+drop cascades to function test_complex_recv(internal)
+drop cascades to function test_complex_send(test_complex_type)
+DROP TYPE test_storage_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_storage_in(cstring)
+drop cascades to function test_storage_out(test_storage_type)
+DROP TYPE test_delim_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_delim_in(cstring)
+drop cascades to function test_delim_out(test_delim_type)
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..295b69cbf9 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -297,3 +297,167 @@ DROP FUNCTION myvarcharsend(myvarchar); -- fail
DROP TYPE myvarchar; -- fail
DROP TYPE myvarchar CASCADE;
+
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+SELECT pg_get_type_ddl('record');
+
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+
+-- Test types with different schemas
+CREATE SCHEMA test_schema;
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+RESET search_path;
+
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+DROP TYPE test_complex_type CASCADE;
+DROP TYPE test_storage_type CASCADE;
+DROP TYPE test_delim_type CASCADE;
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
--
2.50.1 (Apple Git-155)
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');
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
Show quoted text
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. /messages/by-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
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. /messages/by-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.
/messages/by-id/CAPXBC8K5awmtMoq66DGHe+nD7hUf6HPRVHLeGNBRpCDpzusOXQ@mail.gmail.com
3.
/messages/by-id/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
4.
/messages/by-id/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com
5.
/messages/by-id/CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com
6.
/messages/by-id/CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g@mail.gmail.com
--
Best,
Phil Alger
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
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.
Show quoted text
I am submitting a patch as part of the Retail DDL functions project
described here [1].</messages/by-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. /messages/by-id/flat/
CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com
</messages/by-id/flat/
CAPXBC8K5awmtMoq66DGHe%2BnD7hUf6HPRVHLeGNBRpCDpzusOXQ%40mail.gmail.com>
3. /messages/by-id/CAKWEB6rmnmGKUA87Zmq-
s%3Db3Scsnj02C0kObQjnbL2ajfPWGEw%40mail.gmail.com <https://
www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-
s%3Db3Scsnj02C0kObQjnbL2ajfPWGEw%40mail.gmail.com>
4. /messages/by-id/4c5f895e-3281-48f8-
b943-9228b7da6471%40gmail.com <https://www.postgresql.org/message-id/
flat/4c5f895e-3281-48f8-b943-9228b7da6471%40gmail.com>
5. /messages/by-id/flat/
CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com
</messages/by-id/flat/
CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A%40mail.gmail.com>
6. /messages/by-id/flat/
CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g%40mail.gmail.com#b1acf1f04ba8b36239fccdfae0110d3d </messages/by-id/CAPgqM1URzR017U5gEK6S5dYz8VdYMaJf82G9sZFq5xbpHR1J_g@mail.gmail.com--
Best,
Phil Alger
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
Show quoted text
This patch includes documentation, comments, and regression tests, all
of which have run successfully.1. /messages/by-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
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
Attachments:
v2-0001-Add-pg_get_type_ddl-function.patchapplication/octet-stream; name=v2-0001-Add-pg_get_type_ddl-function.patchDownload
From ee90f4ac1521a383458e56e2f797a4fd1f86bbef Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Wed, 8 Oct 2025 19:29:02 -0500
Subject: [PATCH v2] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
This patch implements the pg_get_type_ddl() function, which emits
the DDL for CREATE TYPE. It includes functionality comments in
the code, as well as tests and documentation.
postgres=# SELECT pg_get_type_ddl('t_enum');
pg_get_type_ddl
------------------------------------------------------------
CREATE TYPE public.t_enum AS ENUM ('one', 'two', 'three');
(1 row)
Author: Phil Alger <paalger0@gmail.com>
PG-154
---
doc/src/sgml/func/func-info.sgml | 47 ++
src/backend/utils/adt/ruleutils.c | 536 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 +
src/test/regress/expected/create_type.out | 264 +++++++++++
src/test/regress/sql/create_type.sql | 168 +++++++
5 files changed, 1019 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94..8dbd379a07 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3795,6 +3795,53 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</tgroup>
</table>
+ </sect2>
+ <sect2 id="functions-info-retail">
+ <title>DDL Retail Functions</title>
+
+ <para>
+ The functions describes in <xref linkend="functions-info-retail-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions for each object type.
+ </para>
+
+ <table id="functions-info-retail-table">
+ <title>DDL Retail Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_type_ddl</primary>
+ </indexterm>
+ <function>pg_get_type_ddl</function>
+ ( <parameter>text</parameter> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the underlying <literal>CREATE TYPE</literal> DDL statement for A
+ given user defined type, returning the complete command as text. It generates
+ definitions for composite, enum, range, base, and shell types. An error is raised
+ if the function is provided with an unsupported type, such as a <literal>DOMAIN</literal>
+ since it has its own <literal>CREATE DOMAIN</literal> statement, a pseudo-type, or
+ a multirange type.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</sect2>
</sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..b64833fc95 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -29,11 +29,13 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
+#include "catalog/pg_enum.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_range.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -52,6 +54,7 @@
#include "parser/parse_func.h"
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "parser/parser.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -373,6 +376,10 @@ static int print_function_arguments(StringInfo buf, HeapTuple proctup,
static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void print_function_trftypes(StringInfo buf, HeapTuple proctup);
static void print_function_sqlbody(StringInfo buf, HeapTuple proctup);
+static void print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid);
+static void print_enum_type_def(StringInfo buf, Oid typeid);
+static void print_range_type_def(StringInfo buf, Oid typeid);
+static void print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup);
static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces,
Bitmapset *rels_used);
static void set_deparse_for_query(deparse_namespace *dpns, Query *query,
@@ -2911,6 +2918,535 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+/*
+ * pg_get_type_ddl
+ * Returns the complete "CREATE TYPE ..." statement for the specified type.
+ */
+Datum
+pg_get_type_ddl(PG_FUNCTION_ARGS)
+{
+ text *typeArg = PG_GETARG_TEXT_PP(0);
+ List *names;
+ TypeName *typeStruct;
+ Oid typeid;
+ HeapTuple typeTup;
+ Form_pg_type typ;
+ char typeType;
+ char *nspName;
+ char *typeName;
+ const char *qualifiedTypeName;
+ StringInfoData buf;
+
+ /* Parse the type name using standard PostgreSQL identifier parsing */
+ names = textToQualifiedNameList(typeArg);
+ typeStruct = makeTypeNameFromNameList(names);
+
+ /*
+ * 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))));
+
+ typ = (Form_pg_type) GETSTRUCT(typeTup);
+ typeid = typ->oid;
+ typeType = typ->typtype;
+
+ /* Only support composite, enum, range, shell, and base types */
+ if (typeType == TYPTYPE_DOMAIN)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a domain type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_PSEUDO && typ->typisdefined)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a pseudo-type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_MULTIRANGE)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a multirange type",
+ format_type_be(typeid))));
+ }
+
+ initStringInfo(&buf);
+
+ /* Get the fully schema-qualified type name */
+ nspName = get_namespace_name(typ->typnamespace);
+ typeName = NameStr(typ->typname);
+ qualifiedTypeName = quote_qualified_identifier(nspName, typeName);
+
+ /* Start the CREATE TYPE command */
+ appendStringInfo(&buf, "CREATE TYPE %s", qualifiedTypeName);
+
+ /* Handle shell types (pseudo-types not yet defined) */
+ if (typeType == TYPTYPE_PSEUDO && !typ->typisdefined)
+ {
+ /* Just the CREATE TYPE statement with no definition */
+ appendStringInfo(&buf, ";");
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+ }
+
+ if (typeType == TYPTYPE_COMPOSITE)
+ print_composite_type_def(&buf, typ, typeid);
+ else if (typeType == TYPTYPE_ENUM)
+ print_enum_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_RANGE)
+ print_range_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_BASE)
+ print_base_type_def(&buf, typ, typeTup);
+ else
+ {
+ ReleaseSysCache(typeTup);
+ elog(ERROR, "unrecognized typtype: %d", (int) typeType);
+ }
+
+ appendStringInfo(&buf, ";");
+
+ /* Clean up */
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
+ * print_composite_type_def
+ * Append the definition of a composite type to buf.
+ */
+static void
+print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid)
+{
+ Relation rel;
+ TupleDesc tupdesc;
+ int i;
+ bool first = true;
+
+ if (!OidIsValid(typ->typrelid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("composite type \"%s\" has no associated relation",
+ format_type_be(typeid))));
+
+ rel = relation_open(typ->typrelid, AccessShareLock);
+ tupdesc = RelationGetDescr(rel);
+
+ appendStringInfoString(buf, " AS (");
+
+ /* Loop through each column of the composite type */
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ /* Add comma separator between columns */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ /* Output column name and type */
+ appendStringInfo(buf, "%s %s",
+ quote_identifier(NameStr(attr->attname)),
+ format_type_with_typemod(attr->atttypid,
+ attr->atttypmod));
+
+ /* Output COLLATE clause if there is a different collation than the default */
+ if (attr->attcollation != InvalidOid &&
+ attr->attcollation != get_typcollation(attr->atttypid))
+ appendStringInfo(buf, " COLLATE %s",
+ generate_collation_name(attr->attcollation));
+ }
+
+ appendStringInfoChar(buf, ')');
+ relation_close(rel, AccessShareLock);
+}
+
+/*
+ * print_enum_type_def
+ * Append the definition of an enum type to buf.
+ */
+static void
+print_enum_type_def(StringInfo buf, Oid typeid)
+{
+ Relation enumRel;
+ SysScanDesc enumScan;
+ HeapTuple enumTuple;
+ ScanKeyData skey;
+ bool first = true;
+
+ appendStringInfoString(buf, " AS ENUM (");
+
+ ScanKeyInit(&skey,
+ Anum_pg_enum_enumtypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(typeid));
+
+ enumRel = table_open(EnumRelationId, AccessShareLock);
+ enumScan = systable_beginscan(enumRel, EnumTypIdSortOrderIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(enumTuple = systable_getnext(enumScan)))
+ {
+ Form_pg_enum en = (Form_pg_enum) GETSTRUCT(enumTuple);
+
+ /* Add comma separator between enum values */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ appendStringInfo(buf, "%s",
+ quote_literal_cstr(NameStr(en->enumlabel)));
+ }
+
+ systable_endscan(enumScan);
+ table_close(enumRel, AccessShareLock);
+
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * print_range_type_def
+ * Append the definition of a range type to buf.
+ */
+static void
+print_range_type_def(StringInfo buf, Oid typeid)
+{
+ HeapTuple rngTup;
+ Form_pg_range rng;
+ Oid subtype;
+ Oid subopc;
+ Oid collation;
+ Oid canonical;
+ Oid subdiff;
+ Oid multirange;
+ HeapTuple funcTup;
+ Form_pg_proc funcForm;
+ char *funcNsp;
+
+ rngTup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(typeid));
+ if (!HeapTupleIsValid(rngTup))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("range type \"%s\" has no pg_range entry",
+ format_type_be(typeid))));
+
+ rng = (Form_pg_range) GETSTRUCT(rngTup);
+
+ subtype = rng->rngsubtype;
+ subopc = rng->rngsubopc;
+ collation = rng->rngcollation;
+ canonical = rng->rngcanonical;
+ subdiff = rng->rngsubdiff;
+ multirange = rng->rngmultitypid;
+
+ appendStringInfo(buf, " AS RANGE (SUBTYPE = %s",
+ format_type_be(subtype));
+
+ /* Range type parameters */
+
+ /* Add SUBTYPE_OPCLASS if a specific operator class is defined */
+ if (OidIsValid(subopc))
+ {
+ appendStringInfoString(buf, ", SUBTYPE_OPCLASS =");
+ get_opclass_name(subopc, InvalidOid, buf);
+ }
+
+ /* Add COLLATION if the range type uses a non-default collation */
+ if (OidIsValid(collation))
+ appendStringInfo(buf, ", COLLATION = %s",
+ generate_collation_name(collation));
+
+ /* Add CANONICAL and SUBTYPE_DIFF functions if defined */
+ if (OidIsValid(canonical))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(canonical));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", canonical);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", CANONICAL = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ if (OidIsValid(subdiff))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(subdiff));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", subdiff);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SUBTYPE_DIFF = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* Add MULTIRANGE_TYPE_NAME if a multirange type is associated with this range */
+ if (OidIsValid(multirange))
+ {
+ HeapTuple multirangeTup;
+ Form_pg_type multirangeTyp;
+ char *multirangeNspName;
+ const char *qualifiedMultirangeName;
+
+ multirangeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(multirange));
+ if (!HeapTupleIsValid(multirangeTup))
+ elog(ERROR, "cache lookup failed for type %u", multirange);
+
+ multirangeTyp = (Form_pg_type) GETSTRUCT(multirangeTup);
+ multirangeNspName = get_namespace_name(multirangeTyp->typnamespace);
+ qualifiedMultirangeName = quote_qualified_identifier(multirangeNspName,
+ NameStr(multirangeTyp->typname));
+ appendStringInfo(buf, ", MULTIRANGE_TYPE_NAME = %s",
+ qualifiedMultirangeName);
+
+ ReleaseSysCache(multirangeTup);
+ }
+
+ appendStringInfoChar(buf, ')');
+ ReleaseSysCache(rngTup);
+}
+
+/*
+ * print_base_type_def
+ * Append the definition of a base type to buf.
+ */
+static void
+print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup)
+{
+ Datum defaultDatum;
+ bool isnull;
+ char *defaultValue;
+ HeapTuple funcTup;
+ Form_pg_proc funcForm;
+ char *funcNsp;
+
+ appendStringInfoString(buf, " (");
+
+ /* INPUT function (required) */
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typinput));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typinput);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, "INPUT = %s, ",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+
+ /* OUTPUT function (required) */
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typoutput));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typoutput);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, "OUTPUT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+
+ /* Optional functions if defined */
+
+ /* RECEIVE function */
+ if (OidIsValid(typ->typreceive))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typreceive));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typreceive);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", RECEIVE = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* SEND function */
+ if (OidIsValid(typ->typsend))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typsend));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typsend);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SEND = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* TYPMOD_IN function */
+ if (OidIsValid(typ->typmodin))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typmodin));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typmodin);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", TYPMOD_IN = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* TYPMOD_OUT function */
+ if (OidIsValid(typ->typmodout))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typmodout));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typmodout);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", TYPMOD_OUT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* ANALYZE function */
+ if (OidIsValid(typ->typanalyze))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typanalyze));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typanalyze);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", ANALYZE = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* SUBSCRIPT function */
+ if (OidIsValid(typ->typsubscript))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typsubscript));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typsubscript);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SUBSCRIPT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* INTERNALLENGTH */
+ if (typ->typlen == -1)
+ appendStringInfoString(buf, ", INTERNALLENGTH = VARIABLE");
+ else
+ appendStringInfo(buf, ", INTERNALLENGTH = %d", typ->typlen);
+
+ /* PASSEDBYVALUE */
+ if (typ->typbyval)
+ appendStringInfoString(buf, ", PASSEDBYVALUE");
+
+ /* ALIGNMENT */
+ if (typ->typalign != TYPALIGN_INT)
+ {
+ switch (typ->typalign)
+ {
+ case TYPALIGN_CHAR:
+ appendStringInfoString(buf, ", ALIGNMENT = char");
+ break;
+ case TYPALIGN_SHORT:
+ appendStringInfoString(buf, ", ALIGNMENT = int2");
+ break;
+ case TYPALIGN_DOUBLE:
+ appendStringInfoString(buf, ", ALIGNMENT = double");
+ break;
+ }
+ }
+
+ /* STORAGE */
+ if (typ->typstorage != TYPSTORAGE_PLAIN)
+ {
+ switch (typ->typstorage)
+ {
+ /* Output non-default storage types */
+ case TYPSTORAGE_EXTERNAL:
+ appendStringInfoString(buf, ", STORAGE = external");
+ break;
+ case TYPSTORAGE_EXTENDED:
+ appendStringInfoString(buf, ", STORAGE = extended");
+ break;
+ case TYPSTORAGE_MAIN:
+ appendStringInfoString(buf, ", STORAGE = main");
+ break;
+ }
+ }
+
+ /* CATEGORY */
+ if (typ->typcategory != TYPCATEGORY_USER)
+ appendStringInfo(buf, ", CATEGORY = '%c'", typ->typcategory);
+
+ /* PREFERRED */
+ if (typ->typispreferred)
+ appendStringInfoString(buf, ", PREFERRED = true");
+
+ /* DEFAULT */
+ defaultDatum = SysCacheGetAttr(TYPEOID, typeTup,
+ Anum_pg_type_typdefault, &isnull);
+ if (!isnull)
+ {
+ defaultValue = TextDatumGetCString(defaultDatum);
+ appendStringInfo(buf, ", DEFAULT = %s",
+ quote_literal_cstr(defaultValue));
+ }
+
+ /* ELEMENT */
+ if (OidIsValid(typ->typelem))
+ appendStringInfo(buf, ", ELEMENT = %s",
+ format_type_be(typ->typelem));
+
+ /* DELIMITER */
+ if (typ->typdelim != ',')
+ appendStringInfo(buf, ", DELIMITER = '%c'", typ->typdelim);
+
+ /* COLLATABLE */
+ if (OidIsValid(typ->typcollation))
+ appendStringInfoString(buf, ", COLLATABLE = true");
+
+ appendStringInfoChar(buf, ')');
+}
+
/*
* pg_get_functiondef
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f7..34c76f191f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3999,6 +3999,10 @@
{ oid => '1665', descr => 'name of sequence for a serial column',
proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text',
proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' },
+{ 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' },
{ oid => '2098', descr => 'definition of a function',
proname => 'pg_get_functiondef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_functiondef' },
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 5181c4290b..c5e8166def 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -419,3 +419,267 @@ drop cascades to function myvarcharout(myvarchar)
drop cascades to function myvarcharsend(myvarchar)
drop cascades to function myvarcharrecv(internal,oid,integer)
drop cascades to type myvarchardom
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+ pg_get_type_ddl
+-----------------
+
+(1 row)
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+ERROR: type "i_dont_exist" does not exist
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+ pg_get_type_ddl
+-----------------------------
+ CREATE TYPE public.my_test;
+(1 row)
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+ERROR: type "void" is a pseudo-type
+SELECT pg_get_type_ddl('record');
+ERROR: type "record" is a pseudo-type
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.text (INPUT = pg_catalog.textin, OUTPUT = pg_catalog.textout, RECEIVE = pg_catalog.textrecv, SEND = pg_catalog.textsend, INTERNALLENGTH = VARIABLE, STORAGE = extended, CATEGORY = 'S', PREFERRED = true, COLLATABLE = true);
+(1 row)
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+ERROR: type "my_domain" is a domain type
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+ERROR: type "int4multirange" is a multirange type
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+----------------------------------------------------------------
+ CREATE TYPE public.test_enum AS ENUM ('red', 'green', 'blue');
+(1 row)
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------
+ CREATE TYPE public.test_address AS (street text, city text, state text, zip text);
+(1 row)
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+ pg_get_type_ddl
+-------------------------------------------------------------------------
+ CREATE TYPE public.test_composite_collate AS (field1 text COLLATE "C");
+(1 row)
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.int4range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, CANONICAL = pg_catalog.int4range_canonical, SUBTYPE_DIFF = pg_catalog.int4range_subdiff, MULTIRANGE_TYPE_NAME = pg_catalog.int4multirange);
+(1 row)
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+ pg_get_type_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.my_custom_range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = public.my_custom_multirange);
+(1 row)
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_custom_multirange AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = public.my_custom_name);
+(1 row)
+
+-- Custom multirange_type_name with different schema should show the schema
+CREATE SCHEMA test_schema;
+ALTER TYPE my_custom_name SET SCHEMA test_schema;
+SELECT pg_get_type_ddl('test_custom_multirange');
+ pg_get_type_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_custom_multirange AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = test_schema.my_custom_name);
+(1 row)
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_base_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_base_type is only a shell
+LINE 1: CREATE FUNCTION test_base_out(test_base_type) RETURNS cstrin...
+ ^
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_base_type (INPUT = public.test_base_in, OUTPUT = public.test_base_out, INTERNALLENGTH = 4, PASSEDBYVALUE);
+(1 row)
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_complex_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_out(test_complex_type) RETURNS ...
+ ^
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: return type test_complex_type is only a shell
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_send(test_complex_type) RETURNS...
+ ^
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+ pg_get_type_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_complex_type (INPUT = public.test_complex_in, OUTPUT = public.test_complex_out, RECEIVE = public.test_complex_recv, SEND = public.test_complex_send, INTERNALLENGTH = 4, PASSEDBYVALUE, CATEGORY = 'N');
+(1 row)
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_storage_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_storage_type is only a shell
+LINE 1: CREATE FUNCTION test_storage_out(test_storage_type) RETURNS ...
+ ^
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_storage_type (INPUT = public.test_storage_in, OUTPUT = public.test_storage_out, INTERNALLENGTH = VARIABLE, STORAGE = extended);
+(1 row)
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_delim_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_delim_type is only a shell
+LINE 1: CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstr...
+ ^
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+ pg_get_type_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_delim_type (INPUT = public.test_delim_in, OUTPUT = public.test_delim_out, INTERNALLENGTH = 4, DELIMITER = ';');
+(1 row)
+
+-- Test types with different schemas
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+-------------------------------------------------------------
+ CREATE TYPE test_schema.test_enum AS (f1 integer, f2 text);
+(1 row)
+
+RESET search_path;
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+ pg_get_type_ddl
+------------------------------------------------------------------
+ CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+(1 row)
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+ pg_get_type_ddl
+---------------------------------------------------------------
+ CREATE TYPE "TestSchema"."TestEnum" AS (f1 integer, f2 text);
+(1 row)
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_base_in(cstring)
+drop cascades to function test_base_out(test_base_type)
+DROP TYPE test_complex_type CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to function test_complex_in(cstring)
+drop cascades to function test_complex_out(test_complex_type)
+drop cascades to function test_complex_recv(internal)
+drop cascades to function test_complex_send(test_complex_type)
+DROP TYPE test_storage_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_storage_in(cstring)
+drop cascades to function test_storage_out(test_storage_type)
+DROP TYPE test_delim_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_delim_in(cstring)
+drop cascades to function test_delim_out(test_delim_type)
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..55a94a1172 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -297,3 +297,171 @@ DROP FUNCTION myvarcharsend(myvarchar); -- fail
DROP TYPE myvarchar; -- fail
DROP TYPE myvarchar CASCADE;
+
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+SELECT pg_get_type_ddl('record');
+
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+
+-- Custom multirange_type_name with different schema should show the schema
+CREATE SCHEMA test_schema;
+ALTER TYPE my_custom_name SET SCHEMA test_schema;
+SELECT pg_get_type_ddl('test_custom_multirange');
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+
+-- Test types with different schemas
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+RESET search_path;
+
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+DROP TYPE test_complex_type CASCADE;
+DROP TYPE test_storage_type CASCADE;
+DROP TYPE test_delim_type CASCADE;
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
--
2.50.1 (Apple Git-155)
Re: [PATCH] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
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
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
Attachments:
v3-0001-Add-pg_get_type_ddl-function.patchapplication/octet-stream; name=v3-0001-Add-pg_get_type_ddl-function.patchDownload
From a4a3f847fe964202f35c2573df66bc45dbecfd60 Mon Sep 17 00:00:00 2001
From: Phil Alger <paalger0@gmail.com>
Date: Wed, 8 Oct 2025 19:29:02 -0500
Subject: [PATCH v3] Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
This patch implements the pg_get_type_ddl() function, which emits
the DDL for CREATE TYPE. It includes functionality comments in
the code, as well as tests and documentation.
postgres=# SELECT pg_get_type_ddl('t_enum');
pg_get_type_ddl
------------------------------------------------------------
CREATE TYPE public.t_enum AS ENUM ('one', 'two', 'three');
(1 row)
Author: Phil Alger <paalger0@gmail.com>
PG-154
---
doc/src/sgml/func/func-info.sgml | 47 ++
src/backend/utils/adt/ruleutils.c | 536 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 +
src/test/regress/expected/create_type.out | 264 +++++++++++
src/test/regress/sql/create_type.sql | 168 +++++++
5 files changed, 1019 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94..8dbd379a07 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3795,6 +3795,53 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</tgroup>
</table>
+ </sect2>
+ <sect2 id="functions-info-retail">
+ <title>DDL Retail Functions</title>
+
+ <para>
+ The functions describes in <xref linkend="functions-info-retail-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions for each object type.
+ </para>
+
+ <table id="functions-info-retail-table">
+ <title>DDL Retail Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_type_ddl</primary>
+ </indexterm>
+ <function>pg_get_type_ddl</function>
+ ( <parameter>text</parameter> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the underlying <literal>CREATE TYPE</literal> DDL statement for A
+ given user defined type, returning the complete command as text. It generates
+ definitions for composite, enum, range, base, and shell types. An error is raised
+ if the function is provided with an unsupported type, such as a <literal>DOMAIN</literal>
+ since it has its own <literal>CREATE DOMAIN</literal> statement, a pseudo-type, or
+ a multirange type.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</sect2>
</sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231..72fda5c0a9 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -29,11 +29,13 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
+#include "catalog/pg_enum.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_range.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -52,6 +54,7 @@
#include "parser/parse_func.h"
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "parser/parser.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
@@ -373,6 +376,10 @@ static int print_function_arguments(StringInfo buf, HeapTuple proctup,
static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void print_function_trftypes(StringInfo buf, HeapTuple proctup);
static void print_function_sqlbody(StringInfo buf, HeapTuple proctup);
+static void print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid);
+static void print_enum_type_def(StringInfo buf, Oid typeid);
+static void print_range_type_def(StringInfo buf, Oid typeid);
+static void print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup);
static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces,
Bitmapset *rels_used);
static void set_deparse_for_query(deparse_namespace *dpns, Query *query,
@@ -2911,6 +2918,535 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+/*
+ * pg_get_type_ddl
+ * Returns the complete "CREATE TYPE ..." statement for the specified type.
+ */
+Datum
+pg_get_type_ddl(PG_FUNCTION_ARGS)
+{
+ text *typeArg = PG_GETARG_TEXT_PP(0);
+ List *names;
+ TypeName *typeStruct;
+ Oid typeid;
+ HeapTuple typeTup;
+ Form_pg_type typ;
+ char typeType;
+ char *nspName;
+ char *typeName;
+ const char *qualifiedTypeName;
+ StringInfoData buf;
+
+ /* Parse the type name using standard PostgreSQL identifier parsing */
+ names = textToQualifiedNameList(typeArg);
+ typeStruct = makeTypeNameFromNameList(names);
+
+ /*
+ * Look up the type tuple to allow shell types.
+ */
+ typeTup = LookupTypeName(NULL, typeStruct, NULL, true);
+ if (typeTup == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("type \"%s\" does not exist",
+ TypeNameToString(typeStruct))));
+
+ typ = (Form_pg_type) GETSTRUCT(typeTup);
+ typeid = typ->oid;
+ typeType = typ->typtype;
+
+ /* Only support composite, enum, range, shell, and base types */
+ if (typeType == TYPTYPE_DOMAIN)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a domain type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_PSEUDO && typ->typisdefined)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a pseudo-type",
+ format_type_be(typeid))));
+ }
+
+ if (typeType == TYPTYPE_MULTIRANGE)
+ {
+ ReleaseSysCache(typeTup);
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type \"%s\" is a multirange type",
+ format_type_be(typeid))));
+ }
+
+ initStringInfo(&buf);
+
+ /* Get the fully schema-qualified type name */
+ nspName = get_namespace_name(typ->typnamespace);
+ typeName = NameStr(typ->typname);
+ qualifiedTypeName = quote_qualified_identifier(nspName, typeName);
+
+ /* Start the CREATE TYPE command */
+ appendStringInfo(&buf, "CREATE TYPE %s", qualifiedTypeName);
+
+ /* Handle shell types (pseudo-types not yet defined) */
+ if (typeType == TYPTYPE_PSEUDO && !typ->typisdefined)
+ {
+ /* Just the CREATE TYPE statement with no definition */
+ appendStringInfo(&buf, ";");
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+ }
+
+ if (typeType == TYPTYPE_COMPOSITE)
+ print_composite_type_def(&buf, typ, typeid);
+ else if (typeType == TYPTYPE_ENUM)
+ print_enum_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_RANGE)
+ print_range_type_def(&buf, typeid);
+ else if (typeType == TYPTYPE_BASE)
+ print_base_type_def(&buf, typ, typeTup);
+ else
+ {
+ ReleaseSysCache(typeTup);
+ elog(ERROR, "unrecognized typtype: %d", (int) typeType);
+ }
+
+ appendStringInfo(&buf, ";");
+
+ /* Clean up */
+ ReleaseSysCache(typeTup);
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
+ * print_composite_type_def
+ * Append the definition of a composite type to buf.
+ */
+static void
+print_composite_type_def(StringInfo buf, Form_pg_type typ, Oid typeid)
+{
+ Relation rel;
+ TupleDesc tupdesc;
+ int i;
+ bool first = true;
+
+ if (!OidIsValid(typ->typrelid))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("composite type \"%s\" has no associated relation",
+ format_type_be(typeid))));
+
+ rel = relation_open(typ->typrelid, AccessShareLock);
+ tupdesc = RelationGetDescr(rel);
+
+ appendStringInfoString(buf, " AS (");
+
+ /* Loop through each column of the composite type */
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ /* Add comma separator between columns */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ /* Output column name and type */
+ appendStringInfo(buf, "%s %s",
+ quote_identifier(NameStr(attr->attname)),
+ format_type_with_typemod(attr->atttypid,
+ attr->atttypmod));
+
+ /* Output COLLATE clause if there is a different collation than the default */
+ if (attr->attcollation != InvalidOid &&
+ attr->attcollation != get_typcollation(attr->atttypid))
+ appendStringInfo(buf, " COLLATE %s",
+ generate_collation_name(attr->attcollation));
+ }
+
+ appendStringInfoChar(buf, ')');
+ relation_close(rel, AccessShareLock);
+}
+
+/*
+ * print_enum_type_def
+ * Append the definition of an enum type to buf.
+ */
+static void
+print_enum_type_def(StringInfo buf, Oid typeid)
+{
+ Relation enumRel;
+ SysScanDesc enumScan;
+ HeapTuple enumTuple;
+ ScanKeyData skey;
+ bool first = true;
+
+ appendStringInfoString(buf, " AS ENUM (");
+
+ ScanKeyInit(&skey,
+ Anum_pg_enum_enumtypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(typeid));
+
+ enumRel = table_open(EnumRelationId, AccessShareLock);
+ enumScan = systable_beginscan(enumRel, EnumTypIdSortOrderIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(enumTuple = systable_getnext(enumScan)))
+ {
+ Form_pg_enum en = (Form_pg_enum) GETSTRUCT(enumTuple);
+
+ /* Add comma separator between enum values */
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
+
+ appendStringInfo(buf, "%s",
+ quote_literal_cstr(NameStr(en->enumlabel)));
+ }
+
+ systable_endscan(enumScan);
+ table_close(enumRel, AccessShareLock);
+
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * print_range_type_def
+ * Append the definition of a range type to buf.
+ */
+static void
+print_range_type_def(StringInfo buf, Oid typeid)
+{
+ HeapTuple rngTup;
+ Form_pg_range rng;
+ Oid subtype;
+ Oid subopc;
+ Oid collation;
+ Oid canonical;
+ Oid subdiff;
+ Oid multirange;
+ HeapTuple funcTup;
+ Form_pg_proc funcForm;
+ char *funcNsp;
+
+ rngTup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(typeid));
+ if (!HeapTupleIsValid(rngTup))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("range type \"%s\" has no pg_range entry",
+ format_type_be(typeid))));
+
+ rng = (Form_pg_range) GETSTRUCT(rngTup);
+
+ subtype = rng->rngsubtype;
+ subopc = rng->rngsubopc;
+ collation = rng->rngcollation;
+ canonical = rng->rngcanonical;
+ subdiff = rng->rngsubdiff;
+ multirange = rng->rngmultitypid;
+
+ appendStringInfo(buf, " AS RANGE (SUBTYPE = %s",
+ format_type_be(subtype));
+
+ /* Range type parameters */
+
+ /* Add SUBTYPE_OPCLASS if a specific operator class is defined */
+ if (OidIsValid(subopc))
+ {
+ appendStringInfoString(buf, ", SUBTYPE_OPCLASS =");
+ get_opclass_name(subopc, InvalidOid, buf);
+ }
+
+ /* Add COLLATION if the range type uses a non-default collation */
+ if (OidIsValid(collation))
+ appendStringInfo(buf, ", COLLATION = %s",
+ generate_collation_name(collation));
+
+ /* Add CANONICAL and SUBTYPE_DIFF functions if defined */
+ if (OidIsValid(canonical))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(canonical));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", canonical);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", CANONICAL = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ if (OidIsValid(subdiff))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(subdiff));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", subdiff);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SUBTYPE_DIFF = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* Add MULTIRANGE_TYPE_NAME if a multirange type is associated with this range */
+ if (OidIsValid(multirange))
+ {
+ HeapTuple multirangeTup;
+ Form_pg_type multirangeTyp;
+ char *multirangeNspName;
+ const char *qualifiedMultirangeName;
+
+ multirangeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(multirange));
+ if (!HeapTupleIsValid(multirangeTup))
+ elog(ERROR, "cache lookup failed for type %u", multirange);
+
+ multirangeTyp = (Form_pg_type) GETSTRUCT(multirangeTup);
+ multirangeNspName = get_namespace_name(multirangeTyp->typnamespace);
+ qualifiedMultirangeName = quote_qualified_identifier(multirangeNspName,
+ NameStr(multirangeTyp->typname));
+ appendStringInfo(buf, ", MULTIRANGE_TYPE_NAME = %s",
+ qualifiedMultirangeName);
+
+ ReleaseSysCache(multirangeTup);
+ }
+
+ appendStringInfoChar(buf, ')');
+ ReleaseSysCache(rngTup);
+}
+
+/*
+ * print_base_type_def
+ * Append the definition of a base type to buf.
+ */
+static void
+print_base_type_def(StringInfo buf, Form_pg_type typ, HeapTuple typeTup)
+{
+ Datum defaultDatum;
+ bool isnull;
+ char *defaultValue;
+ HeapTuple funcTup;
+ Form_pg_proc funcForm;
+ char *funcNsp;
+
+ appendStringInfoString(buf, " (");
+
+ /* INPUT function (required) */
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typinput));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typinput);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, "INPUT = %s, ",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+
+ /* OUTPUT function (required) */
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typoutput));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typoutput);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, "OUTPUT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+
+ /* Optional functions if defined */
+
+ /* RECEIVE function */
+ if (OidIsValid(typ->typreceive))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typreceive));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typreceive);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", RECEIVE = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* SEND function */
+ if (OidIsValid(typ->typsend))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typsend));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typsend);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SEND = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* TYPMOD_IN function */
+ if (OidIsValid(typ->typmodin))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typmodin));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typmodin);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", TYPMOD_IN = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* TYPMOD_OUT function */
+ if (OidIsValid(typ->typmodout))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typmodout));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typmodout);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", TYPMOD_OUT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* ANALYZE function */
+ if (OidIsValid(typ->typanalyze))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typanalyze));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typanalyze);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", ANALYZE = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* SUBSCRIPT function */
+ if (OidIsValid(typ->typsubscript))
+ {
+ funcTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(typ->typsubscript));
+ if (!HeapTupleIsValid(funcTup))
+ elog(ERROR, "cache lookup failed for function %u", typ->typsubscript);
+
+ funcForm = (Form_pg_proc) GETSTRUCT(funcTup);
+ funcNsp = get_namespace_name(funcForm->pronamespace);
+
+ appendStringInfo(buf, ", SUBSCRIPT = %s",
+ quote_qualified_identifier(funcNsp,
+ NameStr(funcForm->proname)));
+ ReleaseSysCache(funcTup);
+ }
+
+ /* INTERNALLENGTH */
+ if (typ->typlen == -1)
+ appendStringInfoString(buf, ", INTERNALLENGTH = VARIABLE");
+ else
+ appendStringInfo(buf, ", INTERNALLENGTH = %d", typ->typlen);
+
+ /* PASSEDBYVALUE */
+ if (typ->typbyval)
+ appendStringInfoString(buf, ", PASSEDBYVALUE");
+
+ /* ALIGNMENT */
+ if (typ->typalign != TYPALIGN_INT)
+ {
+ switch (typ->typalign)
+ {
+ case TYPALIGN_CHAR:
+ appendStringInfoString(buf, ", ALIGNMENT = char");
+ break;
+ case TYPALIGN_SHORT:
+ appendStringInfoString(buf, ", ALIGNMENT = int2");
+ break;
+ case TYPALIGN_DOUBLE:
+ appendStringInfoString(buf, ", ALIGNMENT = double");
+ break;
+ }
+ }
+
+ /* STORAGE */
+ if (typ->typstorage != TYPSTORAGE_PLAIN)
+ {
+ switch (typ->typstorage)
+ {
+ /* Output non-default storage types */
+ case TYPSTORAGE_EXTERNAL:
+ appendStringInfoString(buf, ", STORAGE = external");
+ break;
+ case TYPSTORAGE_EXTENDED:
+ appendStringInfoString(buf, ", STORAGE = extended");
+ break;
+ case TYPSTORAGE_MAIN:
+ appendStringInfoString(buf, ", STORAGE = main");
+ break;
+ }
+ }
+
+ /* CATEGORY */
+ if (typ->typcategory != TYPCATEGORY_USER)
+ appendStringInfo(buf, ", CATEGORY = '%c'", typ->typcategory);
+
+ /* PREFERRED */
+ if (typ->typispreferred)
+ appendStringInfoString(buf, ", PREFERRED = true");
+
+ /* DEFAULT */
+ defaultDatum = SysCacheGetAttr(TYPEOID, typeTup,
+ Anum_pg_type_typdefault, &isnull);
+ if (!isnull)
+ {
+ defaultValue = TextDatumGetCString(defaultDatum);
+ appendStringInfo(buf, ", DEFAULT = %s",
+ quote_literal_cstr(defaultValue));
+ }
+
+ /* ELEMENT */
+ if (OidIsValid(typ->typelem))
+ appendStringInfo(buf, ", ELEMENT = %s",
+ format_type_be(typ->typelem));
+
+ /* DELIMITER */
+ if (typ->typdelim != ',')
+ appendStringInfo(buf, ", DELIMITER = '%c'", typ->typdelim);
+
+ /* COLLATABLE */
+ if (OidIsValid(typ->typcollation))
+ appendStringInfoString(buf, ", COLLATABLE = true");
+
+ appendStringInfoChar(buf, ')');
+}
+
/*
* pg_get_functiondef
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f7..34c76f191f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3999,6 +3999,10 @@
{ oid => '1665', descr => 'name of sequence for a serial column',
proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text',
proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' },
+{ 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' },
{ oid => '2098', descr => 'definition of a function',
proname => 'pg_get_functiondef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_functiondef' },
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 5181c4290b..c5e8166def 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -419,3 +419,267 @@ drop cascades to function myvarcharout(myvarchar)
drop cascades to function myvarcharsend(myvarchar)
drop cascades to function myvarcharrecv(internal,oid,integer)
drop cascades to type myvarchardom
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+ pg_get_type_ddl
+-----------------
+
+(1 row)
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+ERROR: type "i_dont_exist" does not exist
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+ pg_get_type_ddl
+-----------------------------
+ CREATE TYPE public.my_test;
+(1 row)
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+ERROR: type "void" is a pseudo-type
+SELECT pg_get_type_ddl('record');
+ERROR: type "record" is a pseudo-type
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.text (INPUT = pg_catalog.textin, OUTPUT = pg_catalog.textout, RECEIVE = pg_catalog.textrecv, SEND = pg_catalog.textsend, INTERNALLENGTH = VARIABLE, STORAGE = extended, CATEGORY = 'S', PREFERRED = true, COLLATABLE = true);
+(1 row)
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+ERROR: type "my_domain" is a domain type
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+ERROR: type "int4multirange" is a multirange type
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+----------------------------------------------------------------
+ CREATE TYPE public.test_enum AS ENUM ('red', 'green', 'blue');
+(1 row)
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------
+ CREATE TYPE public.test_address AS (street text, city text, state text, zip text);
+(1 row)
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+ pg_get_type_ddl
+-------------------------------------------------------------------------
+ CREATE TYPE public.test_composite_collate AS (field1 text COLLATE "C");
+(1 row)
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE pg_catalog.int4range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, CANONICAL = pg_catalog.int4range_canonical, SUBTYPE_DIFF = pg_catalog.int4range_subdiff, MULTIRANGE_TYPE_NAME = pg_catalog.int4multirange);
+(1 row)
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+ pg_get_type_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.my_custom_range AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = public.my_custom_multirange);
+(1 row)
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_custom_multirange AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = public.my_custom_name);
+(1 row)
+
+-- Custom multirange_type_name with different schema should show the schema
+CREATE SCHEMA test_schema;
+ALTER TYPE my_custom_name SET SCHEMA test_schema;
+SELECT pg_get_type_ddl('test_custom_multirange');
+ pg_get_type_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_custom_multirange AS RANGE (SUBTYPE = integer, SUBTYPE_OPCLASS = int4_ops, MULTIRANGE_TYPE_NAME = test_schema.my_custom_name);
+(1 row)
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_base_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_base_type is only a shell
+LINE 1: CREATE FUNCTION test_base_out(test_base_type) RETURNS cstrin...
+ ^
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+ pg_get_type_ddl
+------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_base_type (INPUT = public.test_base_in, OUTPUT = public.test_base_out, INTERNALLENGTH = 4, PASSEDBYVALUE);
+(1 row)
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_complex_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_out(test_complex_type) RETURNS ...
+ ^
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: return type test_complex_type is only a shell
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_complex_type is only a shell
+LINE 1: CREATE FUNCTION test_complex_send(test_complex_type) RETURNS...
+ ^
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+ pg_get_type_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_complex_type (INPUT = public.test_complex_in, OUTPUT = public.test_complex_out, RECEIVE = public.test_complex_recv, SEND = public.test_complex_send, INTERNALLENGTH = 4, PASSEDBYVALUE, CATEGORY = 'N');
+(1 row)
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_storage_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_storage_type is only a shell
+LINE 1: CREATE FUNCTION test_storage_out(test_storage_type) RETURNS ...
+ ^
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+ pg_get_type_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_storage_type (INPUT = public.test_storage_in, OUTPUT = public.test_storage_out, INTERNALLENGTH = VARIABLE, STORAGE = extended);
+(1 row)
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: type "test_delim_type" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+NOTICE: argument type test_delim_type is only a shell
+LINE 1: CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstr...
+ ^
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+ pg_get_type_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TYPE public.test_delim_type (INPUT = public.test_delim_in, OUTPUT = public.test_delim_out, INTERNALLENGTH = 4, DELIMITER = ';');
+(1 row)
+
+-- Test types with different schemas
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+ pg_get_type_ddl
+-------------------------------------------------------------
+ CREATE TYPE test_schema.test_enum AS (f1 integer, f2 text);
+(1 row)
+
+RESET search_path;
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+ pg_get_type_ddl
+------------------------------------------------------------------
+ CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+(1 row)
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+ pg_get_type_ddl
+---------------------------------------------------------------
+ CREATE TYPE "TestSchema"."TestEnum" AS (f1 integer, f2 text);
+(1 row)
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_base_in(cstring)
+drop cascades to function test_base_out(test_base_type)
+DROP TYPE test_complex_type CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to function test_complex_in(cstring)
+drop cascades to function test_complex_out(test_complex_type)
+drop cascades to function test_complex_recv(internal)
+drop cascades to function test_complex_send(test_complex_type)
+DROP TYPE test_storage_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_storage_in(cstring)
+drop cascades to function test_storage_out(test_storage_type)
+DROP TYPE test_delim_type CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function test_delim_in(cstring)
+drop cascades to function test_delim_out(test_delim_type)
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..55a94a1172 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -297,3 +297,171 @@ DROP FUNCTION myvarcharsend(myvarchar); -- fail
DROP TYPE myvarchar; -- fail
DROP TYPE myvarchar CASCADE;
+
+-- Test using the pg_get_type_ddl function
+-- Test NULL value
+SELECT pg_get_type_ddl(NULL);
+
+-- Non-existing type should fail
+SELECT pg_get_type_ddl('i_dont_exist');
+
+-- Test the shell type
+CREATE TYPE my_test;
+SELECT pg_get_type_ddl('my_test');
+
+-- Pseudo-types should fail
+SELECT pg_get_type_ddl('void');
+SELECT pg_get_type_ddl('record');
+
+-- Built-in types with special properties should succeed
+-- should show storage = extended, preferred = true, collatable = true
+SELECT pg_get_type_ddl('text');
+
+-- Domain types should fail
+CREATE DOMAIN my_domain AS varchar;
+SELECT pg_get_type_ddl('my_domain');
+
+-- Multirange types should fail
+SELECT pg_get_type_ddl('int4multirange');
+
+-- Enum types should succeed
+CREATE TYPE test_enum AS ENUM ('red', 'green', 'blue');
+SELECT pg_get_type_ddl('test_enum');
+
+-- Composite types should succeed
+CREATE TYPE test_address AS (
+ street text,
+ city text,
+ state text,
+ zip text
+);
+SELECT pg_get_type_ddl('test_address');
+
+-- Composite type with collation should succeed
+CREATE TYPE test_composite_collate AS (
+ field1 text COLLATE "C"
+);
+SELECT pg_get_type_ddl('test_composite_collate');
+
+-- Range types should succeed
+SELECT pg_get_type_ddl('int4range');
+
+-- Custom range type with default multirange name should succeed
+CREATE TYPE my_custom_range AS RANGE (
+ subtype = integer
+);
+SELECT pg_get_type_ddl('my_custom_range');
+
+-- Custom range type with custom multirange name should succeed
+CREATE TYPE test_custom_multirange AS RANGE (
+ subtype = integer,
+ multirange_type_name = my_custom_name
+);
+SELECT pg_get_type_ddl('test_custom_multirange');
+
+-- Custom multirange_type_name with different schema should show the schema
+CREATE SCHEMA test_schema;
+ALTER TYPE my_custom_name SET SCHEMA test_schema;
+SELECT pg_get_type_ddl('test_custom_multirange');
+
+-- Base type with minimal options should succeed
+CREATE FUNCTION test_base_in(cstring) RETURNS test_base_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_base_out(test_base_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_base_type (
+ INPUT = test_base_in,
+ OUTPUT = test_base_out,
+ INTERNALLENGTH = 4,
+ PASSEDBYVALUE
+);
+SELECT pg_get_type_ddl('test_base_type');
+
+-- Base type with many option should succeed
+CREATE FUNCTION test_complex_in(cstring) RETURNS test_complex_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_out(test_complex_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_recv(internal) RETURNS test_complex_type
+AS 'int4recv' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_complex_send(test_complex_type) RETURNS bytea
+AS 'int4send' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_complex_type (
+ INPUT = test_complex_in,
+ OUTPUT = test_complex_out,
+ RECEIVE = test_complex_recv,
+ SEND = test_complex_send,
+ INTERNALLENGTH = 4,
+ ALIGNMENT = int4,
+ STORAGE = plain,
+ PASSEDBYVALUE,
+ CATEGORY = 'N',
+ PREFERRED = false
+);
+SELECT pg_get_type_ddl('test_complex_type');
+
+-- Base type with storage options should succeed
+CREATE FUNCTION test_storage_in(cstring) RETURNS test_storage_type
+AS 'textin' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_storage_out(test_storage_type) RETURNS cstring
+AS 'textout' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_storage_type (
+ INPUT = test_storage_in,
+ OUTPUT = test_storage_out,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended
+);
+SELECT pg_get_type_ddl('test_storage_type');
+
+-- Base type with delimiter should succeed
+CREATE FUNCTION test_delim_in(cstring) RETURNS test_delim_type
+AS 'int4in' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE FUNCTION test_delim_out(test_delim_type) RETURNS cstring
+AS 'int4out' LANGUAGE internal IMMUTABLE STRICT;
+
+CREATE TYPE test_delim_type (
+ INPUT = test_delim_in,
+ OUTPUT = test_delim_out,
+ INTERNALLENGTH = 4,
+ DELIMITER = ';'
+);
+SELECT pg_get_type_ddl('test_delim_type');
+
+-- Test types with different schemas
+CREATE TYPE test_schema.test_enum AS (f1 int4, f2 text);
+SET search_path TO test_schema, public;
+SELECT pg_get_type_ddl('test_enum');
+RESET search_path;
+
+-- Test types with quoted schema and type names
+CREATE SCHEMA "TestSchema";
+CREATE TYPE "TestSchema".t_enum AS ENUM ('one', 'two', 'three');
+SELECT pg_get_type_ddl('"TestSchema".t_enum');
+
+-- Test types with quoted schema and quoted type names
+CREATE TYPE "TestSchema"."TestEnum" AS (f1 int4, f2 text);
+SELECT pg_get_type_ddl('"TestSchema"."TestEnum"');
+
+-- Clean up
+DROP TYPE my_test;
+DROP TYPE my_domain;
+DROP TYPE test_enum;
+DROP TYPE test_address;
+DROP TYPE test_composite_collate;
+DROP TYPE my_custom_range;
+DROP TYPE test_custom_multirange;
+DROP TYPE test_base_type CASCADE;
+DROP TYPE test_complex_type CASCADE;
+DROP TYPE test_storage_type CASCADE;
+DROP TYPE test_delim_type CASCADE;
+DROP TYPE test_schema.test_enum;
+DROP SCHEMA test_schema;
--
2.50.1 (Apple Git-155)