Patch: Add parse_type Function

From 3da1e483571ca91191f133e0df1de7009483cd9f Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Mon, 5 Feb 2024 09:09:18 -0500
Subject: [PATCH v2] Add parse_type() SQL function

The `parse_type()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID and
typmod suitabld for passing to `format_type()`. This allows one to
derive the formal SQL name for a type from a string that may be an
alias:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('timestamp(4)') p;

            format_type
    --------------------------------
    timestamp(4) without time zone

Also accounts for data typs that require the SQL grammar to be parsed:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('interval second(0)') p;

        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example.
Originally written by Erik Wienhold for use in pgTAP.
---
 doc/src/sgml/func.sgml                    | 32 ++++++++++-
 src/backend/utils/adt/format_type.c       | 70 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           |  4 ++
 src/include/utils/builtins.h              |  2 +
 src/test/regress/expected/create_type.out | 53 +++++++++++++++++
 src/test/regress/sql/create_type.sql      | 28 +++++++++
 6 files changed, 188 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8ef4..5344a870ed 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24754,7 +24754,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -24768,6 +24768,36 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
        </para></entry>
       </row>
 
+      <row>
+       <entry id="parse_type" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>parse_type</primary>
+        </indexterm>
+        <function>parse_type</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>typid</parameter> <type>oid</type>,
+          <parameter>typmod</parameter> <type>int4</type> )
+       </para>
+       <para>
+        Parses a string representing an SQL type declaration as used in a
+        <command>CREATE TABLE</command> statement, optionally schema-qualified.
+        Returns a record with two fields, <parameter>typid</parameter> and
+        <parameter>typmod</parameter>, representing the OID and modifier for the
+        type. These correspond to the parameters to pass to the
+        <link linkend="format_type"><function>format_type</function> function.</link>
+        Returns a null record on null input.
+       </para>
+       <para>
+        For example:
+<programlisting>
+SELECT format_type(p.typid, p.typmod) FROM parse_type('timestamp(4)') p;
+        format_type
+ --------------------------------
+  timestamp(4) without time zone
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/format_type.c b/src/backend/utils/adt/format_type.c
index 28ba0fbd19..91e1064a34 100644
--- a/src/backend/utils/adt/format_type.c
+++ b/src/backend/utils/adt/format_type.c
@@ -26,6 +26,9 @@
 #include "utils/lsyscache.h"
 #include "utils/numeric.h"
 #include "utils/syscache.h"
+#include "fmgr.h"
+#include "funcapi.h"
+#include "parser/parse_type.h"
 
 static char *printTypmod(const char *typname, int32 typmod, Oid typmodout);
 
@@ -482,3 +485,70 @@ oidvectortypes(PG_FUNCTION_ARGS)
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
 }
+
+/*
+ * Given a string that is supposed to be a SQL-compatible type declaration,
+ * such as "int4" or "integer" or "character varying(32)", parse
+ * the string and convert it to a type OID and type modifier.
+ *
+ * Raises an error on an invalid type.
+ */
+
+/*
+ * parse_type() is the inverse of pg_catalog.format_type(): it takes a string
+ * representing an SQL-compatible type declaration, such as "int4" or "integer"
+ * or "character varying(32)", parses it, and returns the OID and type modifier.
+ * Returns NULL for an invalid type.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+PG_FUNCTION_INFO_V1(parse_type);
+
+Datum
+parse_type(PG_FUNCTION_ARGS)
+{
+#define PARSE_TYPE_STRING_COLS 2 /* Returns two columns. */
+	const char *type;            /* the type string we want to resolve */
+	Oid         typid;           /* the resolved type oid */
+	int32       typmod;          /* the resolved type modifier */
+	TupleDesc   tupdesc;
+	HeapTuple   rettuple;
+	Datum       values[PARSE_TYPE_STRING_COLS] = {0};
+	bool        nulls[PARSE_TYPE_STRING_COLS] = {0};
+
+	if (PG_ARGISNULL(0)) {
+        PG_RETURN_NULL();
+	}
+
+	type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	/*
+	 * Build a tuple descriptor for our result type; return an error if not
+	 * called in a context that expects a record.
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {
+		ereport(
+			ERROR,
+			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			errmsg("function returning record called in context that cannot accept type record"))
+		);
+	}
+
+	BlessTupleDesc(tupdesc);
+
+	/*
+	 * Parse type-name argument to obtain type OID and encoded typmod. We don't
+	 * need to check for parseTypeString failure, but just let the error be
+	 * raised. The 0 arg works both as the `Node *escontext` arg in Postgres 16
+	 * and the `bool missing_ok` arg in 9.4-15.
+	 */
+	(void) parseTypeString(type, &typid, &typmod, 0);
+
+	/* Create and return tuple. */
+	values[0] = typid;
+	values[1] = typmod;
+	rettuple = heap_form_tuple(tupdesc, values, nulls);
+	return HeapTupleGetDatum(rettuple);
+#undef PARSE_TYPE_STRING_COLS
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..31d7024010 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2184,6 +2184,10 @@
 { oid => '1081', descr => 'format a type oid and atttypmod to canonical SQL',
   proname => 'format_type', proisstrict => 'f', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid int4', prosrc => 'format_type' },
+{ oid => '8401', descr => 'parse a type string into its a type oid and atttypmod',
+  proname => 'parse_type', proisstrict => 'f', provolatile => 's',
+  prorettype => 'record', proargtypes => 'text', prosrc => 'parse_type',
+  proallargtypes => '{text,oid,int4}', proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}' },
 { oid => '1084', descr => 'I/O',
   proname => 'date_in', provolatile => 's', prorettype => 'date',
   proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 359c570f23..264ad090be 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -133,6 +133,8 @@ extern char *format_type_with_typemod(Oid type_oid, int32 typemod);
 
 extern int32 type_maximum_size(Oid type_oid, int32 typemod);
 
+extern Datum parse_type(PG_FUNCTION_ARGS);
+
 /* quote.c */
 extern char *quote_literal_cstr(const char *rawstr);
 
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 7383fcdbb1..84ea71090c 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -249,6 +249,59 @@ select format_type('bpchar'::regtype, -1);
  bpchar
 (1 row)
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+ typid | typmod 
+-------+--------
+    25 |     -1
+(1 row)
+
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+ typid | typmod 
+-------+--------
+       |       
+(1 row)
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+ERROR:  type "nonesuch" does not exist
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT parse_type('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT parse_type('year(4)'); -- grammar error expected
+ERROR:  type "year" does not exist
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+          format_type           
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+ mytab
+(8 rows)
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
  pg_input_is_valid 
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..e4700b8719 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -192,6 +192,34 @@ select format_type('bpchar'::regtype, null);
 -- this behavior difference is intentional
 select format_type('bpchar'::regtype, -1);
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+SELECT parse_type('year(4)'); -- grammar error expected
+
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
 SELECT pg_input_is_valid('(1,2)', 'widget');  -- hard error expected
-- 
2.43.0

From c514e2900bf4967186c13922bcb9808dcfd3f851 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Mon, 5 Feb 2024 10:12:40 -0500
Subject: [PATCH v3] Add parse_type() SQL function

The `parse_type()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID and
typmod suitabld for passing to `format_type()`. This allows one to
derive the formal SQL name for a type from a string that may be an
alias:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('timestamp(4)') p;

            format_type
    --------------------------------
    timestamp(4) without time zone

Also accounts for data typs that require the SQL grammar to be parsed:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('interval second(0)') p;

        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example.
Originally written by Erik Wienhold for use in pgTAP.
---
 doc/src/sgml/func.sgml                    | 31 +++++++++++-
 src/backend/utils/adt/format_type.c       | 59 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           |  4 ++
 src/include/utils/builtins.h              |  2 +
 src/test/regress/expected/create_type.out | 53 ++++++++++++++++++++
 src/test/regress/sql/create_type.sql      | 28 +++++++++++
 6 files changed, 176 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8ef4..1e2426acae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24754,7 +24754,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -24768,6 +24768,35 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
        </para></entry>
       </row>
 
+      <row>
+       <entry id="parse_type" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>parse_type</primary>
+        </indexterm>
+        <function>parse_type</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>typid</parameter> <type>oid</type>,
+          <parameter>typmod</parameter> <type>int4</type> )
+       </para>
+       <para>
+        Parses a string representing an SQL type declaration as used in a
+        <command>CREATE TABLE</command> statement, optionally schema-qualified.
+        Returns a record with two fields, <parameter>typid</parameter> and
+        <parameter>typmod</parameter>, representing the OID and modifier for the
+        type. These correspond to the parameters to pass to the
+        <link linkend="format_type"><function>format_type</function> function.</link>
+       </para>
+       <para>
+        For example:
+<programlisting>
+SELECT format_type(p.typid, p.typmod) FROM parse_type('timestamp(4)') p;
+        format_type
+ --------------------------------
+  timestamp(4) without time zone
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/format_type.c b/src/backend/utils/adt/format_type.c
index 28ba0fbd19..0d56c8cbb1 100644
--- a/src/backend/utils/adt/format_type.c
+++ b/src/backend/utils/adt/format_type.c
@@ -26,6 +26,9 @@
 #include "utils/lsyscache.h"
 #include "utils/numeric.h"
 #include "utils/syscache.h"
+#include "fmgr.h"
+#include "funcapi.h"
+#include "parser/parse_type.h"
 
 static char *printTypmod(const char *typname, int32 typmod, Oid typmodout);
 
@@ -482,3 +485,59 @@ oidvectortypes(PG_FUNCTION_ARGS)
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
 }
+
+/*
+ * parse_type() is the inverse of pg_catalog.format_type(): it takes a string
+ * representing an SQL-compatible type declaration, such as "int4" or "integer"
+ * or "character varying(32)", parses it, and returns the OID and type modifier.
+ *
+ * Raises an error on an invalid type.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+PG_FUNCTION_INFO_V1(parse_type);
+
+Datum
+parse_type(PG_FUNCTION_ARGS)
+{
+#define PARSE_TYPE_STRING_COLS 2 /* Returns two columns. */
+	const char *type;            /* the type string we want to resolve */
+	Oid         typid;           /* the resolved type oid */
+	int32       typmod;          /* the resolved type modifier */
+	TupleDesc   tupdesc;
+	HeapTuple   rettuple;
+	Datum       values[PARSE_TYPE_STRING_COLS] = {0};
+	bool        nulls[PARSE_TYPE_STRING_COLS] = {0};
+
+	type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	/*
+	 * Build a tuple descriptor for our result type; return an error if not
+	 * called in a context that expects a record.
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {
+		ereport(
+			ERROR,
+			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			errmsg("function returning record called in context that cannot accept type record"))
+		);
+	}
+
+	BlessTupleDesc(tupdesc);
+
+	/*
+	 * Parse type-name argument to obtain type OID and encoded typmod. We don't
+	 * need to check for parseTypeString failure, but just let the error be
+	 * raised. The 0 arg works both as the `Node *escontext` arg in Postgres 16
+	 * and the `bool missing_ok` arg in 9.4-15.
+	 */
+	(void) parseTypeString(type, &typid, &typmod, 0);
+
+	/* Create and return tuple. */
+	values[0] = typid;
+	values[1] = typmod;
+	rettuple = heap_form_tuple(tupdesc, values, nulls);
+	return HeapTupleGetDatum(rettuple);
+#undef PARSE_TYPE_STRING_COLS
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..37205e1b31 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2184,6 +2184,10 @@
 { oid => '1081', descr => 'format a type oid and atttypmod to canonical SQL',
   proname => 'format_type', proisstrict => 'f', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid int4', prosrc => 'format_type' },
+{ oid => '8401', descr => 'parse a type string into its a type oid and atttypmod',
+  proname => 'parse_type', proisstrict => 't', provolatile => 's',
+  prorettype => 'record', proargtypes => 'text', prosrc => 'parse_type',
+  proallargtypes => '{text,oid,int4}', proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}' },
 { oid => '1084', descr => 'I/O',
   proname => 'date_in', provolatile => 's', prorettype => 'date',
   proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 359c570f23..264ad090be 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -133,6 +133,8 @@ extern char *format_type_with_typemod(Oid type_oid, int32 typemod);
 
 extern int32 type_maximum_size(Oid type_oid, int32 typemod);
 
+extern Datum parse_type(PG_FUNCTION_ARGS);
+
 /* quote.c */
 extern char *quote_literal_cstr(const char *rawstr);
 
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 7383fcdbb1..84ea71090c 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -249,6 +249,59 @@ select format_type('bpchar'::regtype, -1);
  bpchar
 (1 row)
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+ typid | typmod 
+-------+--------
+    25 |     -1
+(1 row)
+
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+ typid | typmod 
+-------+--------
+       |       
+(1 row)
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+ERROR:  type "nonesuch" does not exist
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT parse_type('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT parse_type('year(4)'); -- grammar error expected
+ERROR:  type "year" does not exist
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+          format_type           
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+ mytab
+(8 rows)
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
  pg_input_is_valid 
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..e4700b8719 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -192,6 +192,34 @@ select format_type('bpchar'::regtype, null);
 -- this behavior difference is intentional
 select format_type('bpchar'::regtype, -1);
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+SELECT parse_type('year(4)'); -- grammar error expected
+
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
 SELECT pg_input_is_valid('(1,2)', 'widget');  -- hard error expected
-- 
2.43.0

From 72b2e9dc4f677d9d17544fbe23aee0418f76b5cf Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Mon, 12 Feb 2024 11:25:41 -0500
Subject: [PATCH v4] Add parse_type() SQL function

The `parse_type()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID and
typmod suitable for passing to `format_type()`. This allows one to
derive the formal SQL name for a type from a string that may be an
alias:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('timestamp(4)') p;

            format_type
    --------------------------------
    timestamp(4) without time zone

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('interval second(0)') p;

        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example.
Originally written by Erik Wienhold for use in pgTAP.
---
 doc/src/sgml/func.sgml                    | 30 ++++++++++++-
 src/backend/utils/adt/format_type.c       | 52 ++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           |  4 ++
 src/include/utils/builtins.h              |  2 +
 src/test/regress/expected/create_type.out | 53 +++++++++++++++++++++++
 src/test/regress/sql/create_type.sql      | 28 ++++++++++++
 6 files changed, 168 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 11d537b341..adcc433a69 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24759,7 +24759,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -24773,6 +24773,34 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
        </para></entry>
       </row>
 
+      <row>
+       <entry id="parse_type" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>parse_type</primary>
+        </indexterm>
+        <function>parse_type</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>typid</parameter> <type>oid</type>,
+          <parameter>typmod</parameter> <type>integer</type> )
+       </para>
+       <para>
+        Parses a string representing an SQL data type, optionally schema-qualified.
+        Returns a record with two fields, <parameter>typid</parameter> and
+        <parameter>typmod</parameter>, representing the OID and modifier for the
+        type. These correspond to the parameters to pass to the
+        <link linkend="format_type"><function>format_type</function> function.</link>
+       </para>
+       <para>
+        For example:
+<programlisting>
+SELECT format_type(p.typid, p.typmod) FROM parse_type('timestamp(4)') p;
+        format_type
+ --------------------------------
+  timestamp(4) without time zone
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/format_type.c b/src/backend/utils/adt/format_type.c
index 28ba0fbd19..2be1cf9f5e 100644
--- a/src/backend/utils/adt/format_type.c
+++ b/src/backend/utils/adt/format_type.c
@@ -26,6 +26,9 @@
 #include "utils/lsyscache.h"
 #include "utils/numeric.h"
 #include "utils/syscache.h"
+#include "fmgr.h"
+#include "funcapi.h"
+#include "parser/parse_type.h"
 
 static char *printTypmod(const char *typname, int32 typmod, Oid typmodout);
 
@@ -482,3 +485,52 @@ oidvectortypes(PG_FUNCTION_ARGS)
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
 }
+
+/*
+ * parse_type() is the inverse of pg_catalog.format_type(): it takes a string
+ * representing an SQL-compatible type declaration, such as "int4" or "integer"
+ * or "character varying(32)", parses it, and returns the OID and type modifier.
+ *
+ * Raises an error on an invalid type.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+parse_type(PG_FUNCTION_ARGS)
+{
+#define PARSE_TYPE_STRING_COLS 2 /* Returns two columns. */
+	const char *type;            /* the type string we want to resolve */
+	Oid         typid;           /* the resolved type oid */
+	int32       typmod;          /* the resolved type modifier */
+	TupleDesc   tupdesc;
+	HeapTuple   rettuple;
+	Datum       values[PARSE_TYPE_STRING_COLS] = {0};
+	bool        nulls[PARSE_TYPE_STRING_COLS] = {0};
+
+	type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	/*
+	 * Build a tuple descriptor for our result type; return an error if not
+	 * called in a context that expects a record.
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("function returning record called in context that cannot accept type record")));
+
+	BlessTupleDesc(tupdesc);
+
+	/*
+	 * Parse type-name argument to obtain type OID and encoded typmod. We don't
+	 * need to handle parseTypeString failure, just let the error be raised.
+	 */
+	(void) parseTypeString(type, &typid, &typmod, NULL);
+
+	/* Create and return tuple. */
+	values[0] = typid;
+	values[1] = typmod;
+	rettuple = heap_form_tuple(tupdesc, values, nulls);
+	return HeapTupleGetDatum(rettuple);
+#undef PARSE_TYPE_STRING_COLS
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..37205e1b31 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2184,6 +2184,10 @@
 { oid => '1081', descr => 'format a type oid and atttypmod to canonical SQL',
   proname => 'format_type', proisstrict => 'f', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid int4', prosrc => 'format_type' },
+{ oid => '8401', descr => 'parse a type string into its a type oid and atttypmod',
+  proname => 'parse_type', proisstrict => 't', provolatile => 's',
+  prorettype => 'record', proargtypes => 'text', prosrc => 'parse_type',
+  proallargtypes => '{text,oid,int4}', proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}' },
 { oid => '1084', descr => 'I/O',
   proname => 'date_in', provolatile => 's', prorettype => 'date',
   proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 359c570f23..264ad090be 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -133,6 +133,8 @@ extern char *format_type_with_typemod(Oid type_oid, int32 typemod);
 
 extern int32 type_maximum_size(Oid type_oid, int32 typemod);
 
+extern Datum parse_type(PG_FUNCTION_ARGS);
+
 /* quote.c */
 extern char *quote_literal_cstr(const char *rawstr);
 
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index 7383fcdbb1..84ea71090c 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -249,6 +249,59 @@ select format_type('bpchar'::regtype, -1);
  bpchar
 (1 row)
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+ typid | typmod 
+-------+--------
+    25 |     -1
+(1 row)
+
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+ typid | typmod 
+-------+--------
+       |       
+(1 row)
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+ERROR:  type "nonesuch" does not exist
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT parse_type('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT parse_type('year(4)'); -- grammar error expected
+ERROR:  type "year" does not exist
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+          format_type           
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+ mytab
+(8 rows)
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
  pg_input_is_valid 
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index c25018029c..e4700b8719 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -192,6 +192,34 @@ select format_type('bpchar'::regtype, null);
 -- this behavior difference is intentional
 select format_type('bpchar'::regtype, -1);
 
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+SELECT parse_type('year(4)'); -- grammar error expected
+
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)',
+        'mytab'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+
 -- Test non-error-throwing APIs using widget, which still throws errors
 SELECT pg_input_is_valid('(1,2,3)', 'widget');
 SELECT pg_input_is_valid('(1,2)', 'widget');  -- hard error expected
-- 
2.43.1

From 857f7eace0bc65ef882dd8ced51993e37142055c Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Tue, 13 Feb 2024 17:58:34 -0500
Subject: [PATCH v5] Add parse_type() SQL function

The `parse_type()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID and
typmod suitable for passing to `format_type()`. This allows one to
derive the formal SQL name for a type from a string that may be an
alias:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('timestamp(4)') p;

            format_type
    --------------------------------
    timestamp(4) without time zone

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(p.typid, p.typmod)
      FROM parse_type('interval second(0)') p;

        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example.
Originally written by Erik Wienhold for use in pgTAP.
---
 doc/src/sgml/func.sgml                | 30 +++++++++++++++-
 src/backend/utils/adt/regproc.c       | 51 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  6 ++++
 src/test/regress/expected/regproc.out | 51 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 28 +++++++++++++++
 5 files changed, 165 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 11d537b341..adcc433a69 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24759,7 +24759,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -24773,6 +24773,34 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
        </para></entry>
       </row>
 
+      <row>
+       <entry id="parse_type" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>parse_type</primary>
+        </indexterm>
+        <function>parse_type</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>typid</parameter> <type>oid</type>,
+          <parameter>typmod</parameter> <type>integer</type> )
+       </para>
+       <para>
+        Parses a string representing an SQL data type, optionally schema-qualified.
+        Returns a record with two fields, <parameter>typid</parameter> and
+        <parameter>typmod</parameter>, representing the OID and modifier for the
+        type. These correspond to the parameters to pass to the
+        <link linkend="format_type"><function>format_type</function> function.</link>
+       </para>
+       <para>
+        For example:
+<programlisting>
+SELECT format_type(p.typid, p.typmod) FROM parse_type('timestamp(4)') p;
+        format_type
+ --------------------------------
+  timestamp(4) without time zone
+</programlisting>
+       </para></entry>
+      </row>
+
       <row>
        <entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..4fee27a139 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -30,6 +30,7 @@
 #include "catalog/pg_ts_config.h"
 #include "catalog/pg_ts_dict.h"
 #include "catalog/pg_type.h"
+#include "funcapi.h"
 #include "lib/stringinfo.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
@@ -2016,3 +2017,53 @@ parseNameAndArgTypes(const char *string, bool allowNone, List **names,
 
 	return true;
 }
+
+
+/*
+ * parse_type() is the inverse of pg_catalog.format_type(): it takes a string
+ * representing an SQL-compatible type declaration, such as "int4" or "integer"
+ * or "character varying(32)", parses it, and returns the OID and type modifier.
+ *
+ * Raises an error on an invalid type.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+parse_type(PG_FUNCTION_ARGS)
+{
+#define PARSE_TYPE_STRING_COLS 2 /* Returns two columns. */
+	const char *type;            /* the type string we want to resolve */
+	Oid         typid;           /* the resolved type oid */
+	int32       typmod;          /* the resolved type modifier */
+	TupleDesc   tupdesc;
+	HeapTuple   rettuple;
+	Datum       values[PARSE_TYPE_STRING_COLS] = {0};
+	bool        nulls[PARSE_TYPE_STRING_COLS] = {0};
+
+	type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	/*
+	 * Build a tuple descriptor for our result type; return an error if not
+	 * called in a context that expects a record.
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("function returning record called in context that cannot accept type record")));
+
+	BlessTupleDesc(tupdesc);
+
+	/*
+	 * Parse type-name argument to obtain type OID and encoded typmod. We don't
+	 * need to handle parseTypeString failure, just let the error be raised.
+	 */
+	(void) parseTypeString(type, &typid, &typmod, NULL);
+
+	/* Create and return tuple. */
+	values[0] = typid;
+	values[1] = typmod;
+	rettuple = heap_form_tuple(tupdesc, values, nulls);
+	return HeapTupleGetDatum(rettuple);
+#undef PARSE_TYPE_STRING_COLS
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..8071b44f6b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2184,6 +2184,12 @@
 { oid => '1081', descr => 'format a type oid and atttypmod to canonical SQL',
   proname => 'format_type', proisstrict => 'f', provolatile => 's',
   prorettype => 'text', proargtypes => 'oid int4', prosrc => 'format_type' },
+{ oid => '8401',
+  descr => 'parse a type string into its a type oid and atttypmod',
+  proname => 'parse_type', provolatile => 's', prorettype => 'record',
+  proargtypes => 'text', proallargtypes => '{text,oid,int4}',
+  proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}',
+  prosrc => 'parse_type' },
 { oid => '1084', descr => 'I/O',
   proname => 'date_in', provolatile => 's', prorettype => 'date',
   proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..033f6a81a5 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,54 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+ typid | typmod 
+-------+--------
+    25 |     -1
+(1 row)
+
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+ typid | typmod 
+-------+--------
+       |       
+(1 row)
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+ERROR:  type "nonesuch" does not exist
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT parse_type('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT parse_type('year(4)'); -- grammar error expected
+ERROR:  type "year" does not exist
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+          format_type           
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+(7 rows)
+
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..489463aa9e 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,31 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test parse_type
+SELECT * FROM parse_type('text') p(typid, typmod);
+SELECT * FROM parse_type(NULL) p(typid, typmod);
+
+-- Test parse_type errors
+SELECT parse_type('nonesuch'); -- error expected
+SELECT parse_type('interval nonesuch'); -- grammar error expected
+SELECT parse_type('year(4)'); -- grammar error expected
+
+-- Test parse_type with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT ((parse_type(s)).*)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+
-- 
2.43.1

From 0b60432a84d63a7fccaae0fe123a0aa2ae67493b Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Sun, 18 Feb 2024 17:33:35 +0100
Subject: [PATCH] Add to_regtypmod() for benchmarking against parse_type()

---
 src/backend/utils/adt/regproc.c       | 18 ++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 51 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 26 ++++++++++++++
 4 files changed, 98 insertions(+)

diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 4fee27a139..6285dc7192 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -2067,3 +2067,21 @@ parse_type(PG_FUNCTION_ARGS)
 	return HeapTupleGetDatum(rettuple);
 #undef PARSE_TYPE_STRING_COLS
 }
+
+Datum
+to_regtypmod(PG_FUNCTION_ARGS)
+{
+	const char *type;            /* the type string we want to resolve */
+	Oid         typid;           /* the resolved type oid */
+	int32       typmod;          /* the resolved type modifier */
+
+	type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+	/*
+	 * Parse type-name argument to obtain type OID and encoded typmod. We don't
+	 * need to handle parseTypeString failure, just let the error be raised.
+	 */
+	(void) parseTypeString(type, &typid, &typmod, NULL);
+
+	PG_RETURN_INT32(typmod);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6cad101bca..befeaeec37 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2190,6 +2190,9 @@
   proargtypes => 'text', proallargtypes => '{text,oid,int4}',
   proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}',
   prosrc => 'parse_type' },
+{ oid => '8402', descr => 'covert type name to typmod',
+  proname => 'to_regtypmod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypmod' },
 { oid => '1084', descr => 'I/O',
   proname => 'date_in', provolatile => 's', prorettype => 'date',
   proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index 033f6a81a5..c548b320c3 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -595,3 +595,54 @@ SELECT format_type(typid, typmod) FROM p;
  character varying(128)
 (7 rows)
 
+-- Test to_regtypmod
+SELECT * FROM to_regtypmod('text');
+ to_regtypmod 
+--------------
+           -1
+(1 row)
+
+SELECT * FROM to_regtypmod(NULL);
+ to_regtypmod 
+--------------
+             
+(1 row)
+
+-- Test to_regtypmod errors
+SELECT to_regtypmod('nonesuch'); -- error expected
+ERROR:  type "nonesuch" does not exist
+SELECT to_regtypmod('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypmod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT to_regtypmod('year(4)'); -- grammar error expected
+ERROR:  type "year" does not exist
+-- Test to_regtypmod with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT to_regtype(s), to_regtypmod(s)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+          format_type           
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+(7 rows)
+
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index 489463aa9e..ac19737012 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -173,3 +173,29 @@ p(typid, typmod) AS (
 )
 SELECT format_type(typid, typmod) FROM p;
 
+-- Test to_regtypmod
+SELECT * FROM to_regtypmod('text');
+SELECT * FROM to_regtypmod(NULL);
+
+-- Test to_regtypmod errors
+SELECT to_regtypmod('nonesuch'); -- error expected
+SELECT to_regtypmod('interval nonesuch'); -- grammar error expected
+SELECT to_regtypmod('year(4)'); -- grammar error expected
+
+-- Test to_regtypmod with various aliases and grammar-based types
+WITH s(s) AS (
+    SELECT * FROM unnest(ARRAY[
+        'timestamp(4)',
+        'interval(0)',
+        'interval second(0)',
+        'timestamptz',
+        'timestamptz(6)',
+        'varchar',
+        'varchar(128)'
+    ])
+),
+p(typid, typmod) AS (
+    SELECT to_regtype(s), to_regtypmod(s)
+      FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
-- 
2.43.2

From 8ac7bf10036d762003702e2c2f84df45051e82f6 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Mon, 19 Feb 2024 21:55:33 -0500
Subject: [PATCH v6] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing  as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.
---
 doc/src/sgml/func.sgml                | 29 +++++++++++-
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 30 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 141 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cf3de80394..57cfbe19f5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24764,7 +24764,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25462,7 +25462,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25477,6 +25477,31 @@ SELECT collation for ('foo' COLLATE "de_DE");
         not found.
        </para></entry>
       </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string representing an SQL data type, optionally schema-qualified, and
+        returns its typmod, modifier for the type. Complements <xref linkend="to_regtype"/>,
+        and can be passed to <xref linkend="format_type" />.
+       </para>
+       <para>
+        For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
+       </para></entry>
+      </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..0eb24ffe57 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -30,6 +30,7 @@
 #include "catalog/pg_ts_config.h"
 #include "catalog/pg_ts_dict.h"
 #include "catalog/pg_type.h"
+#include "funcapi.h"
 #include "lib/stringinfo.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
@@ -1220,6 +1221,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..2e1dd92ffd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7162,6 +7162,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..2f99d22cc9 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL); -- null expected
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error expected
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+SELECT to_regtypemod('year(4)'); -- grammar error expected
+ to_regtypemod 
+---------------
+              
+(1 row)
+
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..bf83d77fab 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL); -- null expected
+SELECT to_regtypemod('interval nonesuch'); -- grammar error expected
+SELECT to_regtypemod('year(4)'); -- grammar error expected
-- 
2.43.2

From dd11116416a78d7fc4ae7f2f549dc1ad987b199d Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Tue, 20 Feb 2024 09:43:48 -0500
Subject: [PATCH v7] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing  as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.

Like `to_regtype()`, `to_regtypemod()` raises an error when the
`parseTypeString()` function drops into the SQL grammar parser raises
an error. So invalid type names like `inteval` or `interval nonesuch`
raise an error, rather than return `NULL`. Note this behavior in the
docs, and also update the documentation for `to_regtype()` to note
this behavior.

While at it, fix an "use of undefined value in sprintf" error in
`genbki.pl` that arose when I had specified `prorettype => 'integer'`
instead of `prorettype => 'int4'` in `pg_proc.dat`. I only realized the
invalid type name when I fixed the Perl bug.
---
 doc/src/sgml/func.sgml                | 40 +++++++++++++---
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 29 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 146 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cf3de80394..56c5d7b097 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24764,7 +24764,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25462,7 +25462,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25470,13 +25470,39 @@ SELECT collation for ('foo' COLLATE "de_DE");
         <returnvalue>regtype</returnvalue>
        </para>
        <para>
-        Translates a textual type name to its OID.  A similar result is
-        obtained by casting the string to type <type>regtype</type> (see
-        <xref linkend="datatype-oid"/>); however, this function will return
-        <literal>NULL</literal> rather than throwing an error if the name is
-        not found.
+        Parses a string of text, extracts a potential type name from it, and
+        translates that name into an OID. A similar result is obtained by
+        casting the string to type <type>regtype</type> (see
+        <xref linkend="datatype-oid"/>). Failure to extract a valid potential
+        type name results in an error; however, if the extracted name is not
+        known to the system, this function will return <literal>NULL</literal>.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string of text, extracts a potential type name from it, and
+        translates its typmod, the modifier for the type, if any. Failure to
+        extract a valid potential type name results in an error; however, if
+        the extracted name is not known to the system, this function will
+        return <literal>NULL</literal>. Complements <xref linkend="to_regtype"/>,
+        and can be passed to <xref linkend="format_type" />. For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
        </para></entry>
       </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..75b8429e0d 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1220,6 +1220,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..2e1dd92ffd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7162,6 +7162,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..ff1ee5b52c 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..c1717e1f8f 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
-- 
2.43.2

	2
<,v8-0001-Add-to_regtypemod-SQL-function.patch

From 8b918759fd6f2328ac06a17c69a0990e2748e834 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Wed, 21 Feb 2024 10:12:08 -0500
Subject: [PATCH v8] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing  as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.

Like `to_regtype()`, `to_regtypemod()` raises an error when the
`parseTypeString()` function drops into the SQL grammar parser raises
an error. So invalid type names like `inteval` or `interval nonesuch`
raise an error, rather than return `NULL`. Note this behavior in the
docs, and also update the documentation for `to_regtype()` to note
this behavior.

While at it, fix an "use of undefined value in sprintf" error in
`genbki.pl` that arose when I had specified `prorettype => 'integer'`
instead of `prorettype => 'int4'` in `pg_proc.dat`. I only realized the
invalid type name when I fixed the Perl bug.
---
 doc/src/sgml/func.sgml                | 40 +++++++++++++---
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 29 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 146 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..4c3ffeb5e4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24763,7 +24763,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" xreflabel="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25461,7 +25461,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" xreflabel="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25469,13 +25469,39 @@ SELECT collation for ('foo' COLLATE "de_DE");
         <returnvalue>regtype</returnvalue>
        </para>
        <para>
-        Translates a textual type name to its OID.  A similar result is
-        obtained by casting the string to type <type>regtype</type> (see
-        <xref linkend="datatype-oid"/>); however, this function will return
-        <literal>NULL</literal> rather than throwing an error if the name is
-        not found.
+        Parses a string of text, extracts a potential type name from it, and
+        translates that name into an OID. A similar result is obtained by
+        casting the string to type <type>regtype</type> (see
+        <xref linkend="datatype-oid"/>). Failure to extract a valid potential
+        type name results in an error; however, if the extracted name is not
+        known to the system, this function will return <literal>NULL</literal>.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <parameter>type</parameter> <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string of text, extracts a potential type name from it, and
+        translates its type modifier, if any. Failure to extract a valid
+        potential type name results in an error; however, if the extracted name
+        is not known to the system, this function will return
+        <literal>NULL</literal>. Complements <xref linkend="to_regtype"/>, and
+        can be passed to <xref linkend="format_type" />. For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
        </para></entry>
       </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..75b8429e0d 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1220,6 +1220,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..2e1dd92ffd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7162,6 +7162,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..ff1ee5b52c 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..c1717e1f8f 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
-- 
2.43.2

From f3606fdb98eeba3b5d61eae04efcaf6b6385efbf Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Wed, 21 Feb 2024 16:47:40 -0500
Subject: [PATCH v9] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing  as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.

Like `to_regtype()`, `to_regtypemod()` raises an error when the
`parseTypeString()` function drops into the SQL grammar parser raises
an error. So invalid type names like `inteval` or `interval nonesuch`
raise an error, rather than return `NULL`. Note this behavior in the
docs, and also update the documentation for `to_regtype()` to note
this behavior.

While at it, fix an "use of undefined value in sprintf" error in
`genbki.pl` that arose when I had specified `prorettype => 'integer'`
instead of `prorettype => 'int4'` in `pg_proc.dat`. I only realized the
invalid type name when I fixed the Perl bug.
---
 doc/src/sgml/func.sgml                | 40 +++++++++++++---
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 29 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 146 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..92c9b9d1c0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24763,7 +24763,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" xreflabel="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25461,7 +25461,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" xreflabel="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25469,13 +25469,39 @@ SELECT collation for ('foo' COLLATE "de_DE");
         <returnvalue>regtype</returnvalue>
        </para>
        <para>
-        Translates a textual type name to its OID.  A similar result is
-        obtained by casting the string to type <type>regtype</type> (see
-        <xref linkend="datatype-oid"/>); however, this function will return
-        <literal>NULL</literal> rather than throwing an error if the name is
-        not found.
+        Parses a string of text, extracts a potential type name from it, and
+        translates that name into an OID. A similar result is obtained by
+        casting the string to type <type>regtype</type> (see
+        <xref linkend="datatype-oid"/>). Failure to extract a valid potential
+        type name results in an error; however, if the extracted name is not
+        known to the system, this function will return <literal>NULL</literal>.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string of text, extracts a potential type name from it, and
+        translates its type modifier, if any. Failure to extract a valid
+        potential type name results in an error; however, if the extracted name
+        is not known to the system, this function will return
+        <literal>NULL</literal>. Complements <xref linkend="to_regtype"/>, and
+        can be passed to <xref linkend="format_type" />. For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
        </para></entry>
       </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..75b8429e0d 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1220,6 +1220,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..2e1dd92ffd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7162,6 +7162,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..ff1ee5b52c 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..c1717e1f8f 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
-- 
2.43.2

From f878a001ff18b826dddb6cab98a5f703ebc9a2e1 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Thu, 7 Mar 2024 20:32:23 -0500
Subject: [PATCH v10] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.

Like `to_regtype()`, `to_regtypemod()` raises an error when the
`parseTypeString()` function drops into the SQL grammar parser raises
an error. So invalid type names like `inteval` or `interval nonesuch`
raise an error, rather than return `NULL`. Note this behavior in the
docs, and also update the documentation for `to_regtype()` to note
this behavior and cite examples.

While at it, fix an "use of undefined value in sprintf" error in
`genbki.pl` that arose when I had specified `prorettype => 'integer'`
instead of `prorettype => 'int4'` in `pg_proc.dat`. I only realized the
invalid type name when I fixed the Perl bug.
---
 doc/src/sgml/func.sgml                | 54 +++++++++++++++++++---
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 29 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 160 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..07e5207af6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24763,7 +24763,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" xreflabel="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25461,7 +25461,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" xreflabel="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25469,13 +25469,53 @@ SELECT collation for ('foo' COLLATE "de_DE");
         <returnvalue>regtype</returnvalue>
        </para>
        <para>
-        Translates a textual type name to its OID.  A similar result is
-        obtained by casting the string to type <type>regtype</type> (see
-        <xref linkend="datatype-oid"/>); however, this function will return
-        <literal>NULL</literal> rather than throwing an error if the name is
-        not found.
+        Parses a string of text, extracts a potential type name from it, and
+        translates that name into an OID. A similar result is obtained by
+        casting the string to type <type>regtype</type> (see
+        <xref linkend="datatype-oid"/>). Failure to extract a valid potential
+        type name results in an error. For example:
+<programlisting>
+SELECT to_regtype('party');
+ to_regtype
+------------
+
+</programlisting>
+        However, if the extracted name is not known to the system, this function
+        will return <literal>NULL</literal>. For example:
+<programlisting>
+SELECT to_regtype('interval nonesuch');
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: select to_regtype('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+</programlisting>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string of text, extracts a potential type name from it, and
+        translates its type modifier, if any. Failure to extract a valid
+        potential type name results in an error; however, if the extracted name
+        is not known to the system, this function will return
+        <literal>NULL</literal>. Complements <xref linkend="to_regtype"/>, and
+        can be passed to <xref linkend="format_type" />. For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
        </para></entry>
       </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..75b8429e0d 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1220,6 +1220,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 291ed876fc..48feafaad4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7155,6 +7155,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..ff1ee5b52c 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..c1717e1f8f 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
-- 
2.44.0

From 2a58ef7d7c8d3d7558e0f21941c4707bedb4eda3 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <david@justatheory.com>
Date: Fri, 8 Mar 2024 20:40:43 -0500
Subject: [PATCH v11] Add to_regtypemod() SQL function

The `to_regtypemod()` function uses the underlying `parseTypeString()` C
function to parse a string representing a data type into a type ID
(which id discards) and a typmod, which it returns. This value is
suitable for passing as the second argument to `format_type()`, which
allow one to derive the formal SQL typmod from a string:

    SELECT format_type(
      to_regtype('varchar(32)'),
      to_regtypemod('varchar(32)')
    );
          format_type
    -----------------------
     character varying(32)

This function also resolves types whose typmod is determined by the SQL
parser or some step after that, such as interval types where the stored
field option is encoded in the typmod:

    SELECT format_type(
      to_regtype('interval second(0)'),
      to_regtypemod('interval second(0)')
    );
        format_type
    --------------------
    interval second(0)

Useful for unit tests for against column data types, for example. Based
on code originally written by Erik Wienhold for use in pgTAP.

Like `to_regtype()`, `to_regtypemod()` raises an error when the
`parseTypeString()` function drops into the SQL grammar parser raises
an error. So invalid type names like `inteval` or `interval nonesuch`
raise an error, rather than return `NULL`. Note this behavior in the
docs, and also update the documentation for `to_regtype()` to note
this behavior and cite examples.

While at it, fix an "use of undefined value in sprintf" error in
`genbki.pl` that arose when I had specified `prorettype => 'integer'`
instead of `prorettype => 'int4'` in `pg_proc.dat`. I only realized the
invalid type name when I fixed the Perl bug.
---
 doc/src/sgml/func.sgml                | 54 +++++++++++++++++++---
 src/backend/catalog/genbki.pl         |  2 +-
 src/backend/utils/adt/regproc.c       | 29 ++++++++++++
 src/include/catalog/pg_proc.dat       |  3 ++
 src/test/regress/expected/regproc.out | 66 +++++++++++++++++++++++++++
 src/test/regress/sql/regproc.sql      | 14 ++++++
 6 files changed, 160 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0bb7aeb40e..18908c8a48 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24763,7 +24763,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
 
      <tbody>
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="format_type" xreflabel="format_type" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>format_type</primary>
         </indexterm>
@@ -25461,7 +25461,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
       </row>
 
       <row>
-       <entry role="func_table_entry"><para role="func_signature">
+       <entry id="to_regtype" xreflabel="to_regtype" role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>to_regtype</primary>
         </indexterm>
@@ -25469,13 +25469,53 @@ SELECT collation for ('foo' COLLATE "de_DE");
         <returnvalue>regtype</returnvalue>
        </para>
        <para>
-        Translates a textual type name to its OID.  A similar result is
-        obtained by casting the string to type <type>regtype</type> (see
-        <xref linkend="datatype-oid"/>); however, this function will return
-        <literal>NULL</literal> rather than throwing an error if the name is
-        not found.
+        Parses a string of text, extracts a potential type name from it, and
+        translates that name into an OID. A similar result is obtained by
+        casting the string to type <type>regtype</type> (see
+        <xref linkend="datatype-oid"/>). Failure to extract a valid potential
+        type name results in an error. For example:
+<programlisting>
+SELECT to_regtype('interval nonesuch');
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: select to_regtype('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
+</programlisting>
+        However, if the extracted name is not known to the system, this function
+        will return <literal>NULL</literal>. For example:
+<programlisting>
+SELECT to_regtype('party');
+ to_regtype
+------------
+
+</programlisting>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry id="to_regtypemod" role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regtypemod</primary>
+        </indexterm>
+        <function>to_regtypemod</function> ( <type>text</type> )
+        <returnvalue>integer</returnvalue>
+       </para>
+       <para>
+        Parses a string of text, extracts a potential type name from it, and
+        translates its type modifier, if any. Failure to extract a valid
+        potential type name results in an error; however, if the extracted name
+        is not known to the system, this function will return
+        <literal>NULL</literal>. Complements <xref linkend="to_regtype"/>, and
+        can be passed to <xref linkend="format_type" />. For example:
+<programlisting>
+SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
+      format_type
+-----------------------
+ character varying(32)
+</programlisting>
        </para></entry>
       </row>
+
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 94afdc5491..a0c299f067 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -1098,7 +1098,7 @@ sub lookup_oids
 			{
 				warn sprintf
 				  "unresolved OID reference \"%s\" in %s.dat field %s line %s\n",
-				  $lookupname, $catname, $attname, $bki_values->{line_number};
+				  $lookupname || '', $catname || '', $attname || '', $bki_values->{line_number} || '';
 				$num_errors++;
 			}
 		}
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 1e3bf3f5fd..75b8429e0d 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -1220,6 +1220,35 @@ to_regtype(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+
+/*
+ * to_regtypemod() complements to_regtype, returning the typmod for the type,
+ * if any.
+ *
+ * If the type name is not found, we return NULL.
+ *
+ * Internally it relies on the Postgres core parseTypeString() function defined
+ * in src/backend/parser/parse_type.c.
+ */
+Datum
+to_regtypemod(PG_FUNCTION_ARGS)
+{
+	char	   *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	Oid         typid;
+	int32       typmod;
+	ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+	/*
+	 * Parse type-name argument to obtain the encoded typmod. Return NULL
+	 * on failure.
+	 */
+	if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext)) {
+		PG_RETURN_NULL();
+	}
+
+	PG_RETURN_INT32(typmod);
+}
+
 /*
  * regtypeout		- converts type OID to "typ_name"
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 291ed876fc..48feafaad4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7155,6 +7155,9 @@
 { oid => '3493', descr => 'convert type name to regtype',
   proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
   proargtypes => 'text', prosrc => 'to_regtype' },
+{ oid => '8401', descr => 'convert type name to type mod',
+  proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
+  proargtypes => 'text', prosrc => 'to_regtypemod' },
 { oid => '1079', descr => 'convert text to regclass',
   proname => 'regclass', provolatile => 's', prorettype => 'regclass',
   proargtypes => 'text', prosrc => 'text_regclass' },
diff --git a/src/test/regress/expected/regproc.out b/src/test/regress/expected/regproc.out
index a9420850b8..ff1ee5b52c 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -544,3 +544,69 @@ SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 ERROR:  improper qualified name (too many dotted names): way.too.many.names
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
 ERROR:  cross-database references are not implemented: no_such_catalog.schema.name
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamp(4)');
+ to_regtypemod 
+---------------
+             4
+(1 row)
+
+SELECT to_regtypemod('interval(0)');
+ to_regtypemod 
+---------------
+    2147418112
+(1 row)
+
+SELECT to_regtypemod('interval second(0)');
+ to_regtypemod 
+---------------
+     268435456
+(1 row)
+
+SELECT to_regtypemod('timestamptz');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('timestamptz(6)');
+ to_regtypemod 
+---------------
+             6
+(1 row)
+
+SELECT to_regtypemod('varchar');
+ to_regtypemod 
+---------------
+            -1
+(1 row)
+
+SELECT to_regtypemod('varchar(128)');
+ to_regtypemod 
+---------------
+           132
+(1 row)
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+ to_regtypemod 
+---------------
+              
+(1 row)
+
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
+ERROR:  syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypemod('interval nonesuch');
+                 ^
+CONTEXT:  invalid type name "interval nonesuch"
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index de2aa881a8..c1717e1f8f 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -145,3 +145,17 @@ SELECT * FROM pg_input_error_info('incorrect type name syntax', 'regtype');
 SELECT * FROM pg_input_error_info('numeric(1,2,3)', 'regtype');  -- bogus typmod
 SELECT * FROM pg_input_error_info('way.too.many.names', 'regtype');
 SELECT * FROM pg_input_error_info('no_such_catalog.schema.name', 'regtype');
+
+-- Test to_regtypemod
+SELECT to_regtypemod('text');
+SELECT to_regtypemod('timestamp(4)');
+SELECT to_regtypemod('interval(0)');
+SELECT to_regtypemod('interval second(0)');
+SELECT to_regtypemod('timestamptz');
+SELECT to_regtypemod('timestamptz(6)');
+SELECT to_regtypemod('varchar');
+SELECT to_regtypemod('varchar(128)');
+
+SELECT to_regtypemod(NULL);                -- returns null on null input
+SELECT to_regtypemod('year(4)');           -- error trapped, returns null
+SELECT to_regtypemod('interval nonesuch'); -- grammar error raised, not trapped
-- 
2.44.0