v2-0001-Add-pg_get_type_ddl-function.patch
application/octet-stream
Filename: v2-0001-Add-pg_get_type_ddl-function.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0001
Subject: Add pg_get_type_ddl() to retrieve the CREATE TYPE statement
| File | + | − |
|---|---|---|
| doc/src/sgml/func/func-info.sgml | 47 | 0 |
| src/backend/utils/adt/ruleutils.c | 536 | 0 |
| src/include/catalog/pg_proc.dat | 4 | 0 |
| src/test/regress/expected/create_type.out | 264 | 0 |
| src/test/regress/sql/create_type.sql | 168 | 0 |
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)