Hi,
the following patch allows to retrieve the typemod. Without this patch,
it does not seem to be possible to generate the first column.
SELECT format_type(to_regtype(t), pg_to_typemod(t)),
format_type(to_regtype(t), NULL)
FROM (VALUES
('INTERVAL SECOND (5)'),
('Varchar(17)'),
('timestamptz (2)')) AS x(t);
format_type | format_type
-----------------------------+--------------------------
interval second(5) | interval
character varying(17) | character varying
timestamp(2) with time zone | timestamp with time zone
I did not find any advice on how to choose a new OID for pg_proc.
Best,
Sophie
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 698daf69ea..b0211fcad2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17126,6 +17126,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
<entry>get the path in the file system that this tablespace is located in</entry>
</row>
<row>
+ <entry><literal><function>pg_to_typemod(<parameter>type_name</parameter>)</function></literal></entry>
+ <entry><type>integer</type></entry>
+ <entry>get the typemod of the named type</entry>
+ </row>
+ <row>
<entry><literal><function>pg_typeof(<parameter>any</parameter>)</function></literal></entry>
<entry><type>regtype</type></entry>
<entry>get the data type of any value</entry>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 1980ff5ac7..ac7407325a 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -30,6 +30,7 @@
#include "funcapi.h"
#include "miscadmin.h"
#include "pgstat.h"
+#include "parser/parse_type.h"
#include "parser/scansup.h"
#include "postmaster/syslogger.h"
#include "rewrite/rewriteHandler.h"
@@ -1002,3 +1003,24 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
else
PG_RETURN_NULL();
}
+
+/*
+ * pg_to_typemod - extracts typemod from "typename"
+ *
+ * This functions suplements to_regtype to obtain the required arguments for
+ * format_type(type_oid, typemod).
+ */
+Datum
+pg_to_typemod(PG_FUNCTION_ARGS)
+{
+ char *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Oid oid;
+ int32 result;
+
+ parseTypeString(typ_name, &oid, &result, true);
+
+ if (OidIsValid(oid))
+ PG_RETURN_INT32(result);
+ else
+ PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c969375981..c40603770b 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1202,6 +1202,8 @@ DATA(insert OID = 972 ( hashbpcharextended PGNSP PGUID 12 1 0 0 0 f f f f t f
DESCR("hash");
DATA(insert OID = 1081 ( format_type PGNSP PGUID 12 1 0 0 0 f f f f f f s s 2 0 25 "26 23" _null_ _null_ _null_ _null_ _null_ format_type _null_ _null_ _null_ ));
DESCR("format a type oid and atttypmod to canonical SQL");
+DATA(insert OID = 4569 ( pg_to_typemod PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 23 "25" _null_ _null_ _null_ _null_ _null_ pg_to_typemod _null_ _null_ _null_ ));
+DESCR("get the typemod of the named type");
DATA(insert OID = 1084 ( date_in PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 1082 "2275" _null_ _null_ _null_ _null_ _null_ date_in _null_ _null_ _null_ ));
DESCR("I/O");
DATA(insert OID = 1085 ( date_out PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2275 "1082" _null_ _null_ _null_ _null_ _null_ date_out _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 130a0e4be3..0584b22155 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -133,3 +133,26 @@ ERROR: function num_nulls() does not exist
LINE 1: SELECT num_nulls();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+---
+--- pg_get_typemod()
+---
+SELECT format_type(to_regtype(t), pg_to_typemod(t)) FROM (VALUES ('INTERVAL SECOND (5)'), ('Varchar(17)'), ('timestamptz (2)')) x(t);
+ format_type
+-----------------------------
+ interval second(5)
+ character varying(17)
+ timestamp(2) with time zone
+(3 rows)
+
+SELECT pg_to_typemod('int');
+ pg_to_typemod
+---------------
+ -1
+(1 row)
+
+SELECT pg_to_typemod('"Unknown Type"') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 1a20c1f765..24d89df1e5 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -29,3 +29,11 @@ SELECT num_nulls(VARIADIC '{}'::int[]);
-- should fail, one or more arguments is required
SELECT num_nonnulls();
SELECT num_nulls();
+
+---
+--- pg_get_typemod()
+---
+
+SELECT format_type(to_regtype(t), pg_to_typemod(t)) FROM (VALUES ('INTERVAL SECOND (5)'), ('Varchar(17)'), ('timestamptz (2)')) x(t);
+SELECT pg_to_typemod('int');
+SELECT pg_to_typemod('"Unknown Type"') IS NULL;
