Skip to content

Commit 1218ca9

Browse files
tglsfdctglsfdc
committed
Add to_regtypemod function to extract typemod from a string type name.
In combination with to_regtype, this allows converting a string to the "canonicalized" form emitted by format_type. That usage requires parsing the string twice, which is slightly annoying but not really too expensive. We considered alternatives such as returning a record type, but that way was notationally uglier than this, and possibly less flexible. Like to_regtype(), we'd rather that this return NULL for any bad input, but the underlying type-parsing logic isn't yet capable of not throwing syntax errors. Adjust the documentation for both functions to point that out. In passing, fix up a couple of nearby entries in the System Catalog Information Functions table that had not gotten the word about our since-v13 convention for displaying function usage examples. David Wheeler and Erik Wienhold, reviewed by Pavel Stehule, Jim Jones, and others. Discussion: https://postgr.es/m/DF2324CA-2673-4ABE-B382-26B5770B6AA3@justatheory.com
1 parent 8068676 commit 1218ca9

File tree

6 files changed

+115
-32
lines changed

6 files changed

+115
-32
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 46 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -24872,7 +24872,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
2487224872

2487324873
<tbody>
2487424874
<row>
24875-
<entry role="func_table_entry"><para role="func_signature">
24875+
<entry id="format-type" xreflabel="format_type" role="func_table_entry"><para role="func_signature">
2487624876
<indexterm>
2487724877
<primary>format_type</primary>
2487824878
</indexterm>
@@ -25387,18 +25387,8 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
2538725387
OID for comparison purposes but displays as a type name.
2538825388
</para>
2538925389
<para>
25390-
For example:
25391-
<programlisting>
25392-
SELECT pg_typeof(33);
25393-
pg_typeof
25394-
-----------
25395-
integer
25396-
25397-
SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
25398-
typlen
25399-
--------
25400-
4
25401-
</programlisting>
25390+
<literal>pg_typeof(33)</literal>
25391+
<returnvalue>integer</returnvalue>
2540225392
</para></entry>
2540325393
</row>
2540425394

@@ -25418,18 +25408,12 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
2541825408
collatable data type, then an error is raised.
2541925409
</para>
2542025410
<para>
25421-
For example:
25422-
<programlisting>
25423-
SELECT collation for (description) FROM pg_description LIMIT 1;
25424-
pg_collation_for
25425-
------------------
25426-
"default"
25427-
25428-
SELECT collation for ('foo' COLLATE "de_DE");
25429-
pg_collation_for
25430-
------------------
25431-
"de_DE"
25432-
</programlisting>
25411+
<literal>collation for ('foo'::text)</literal>
25412+
<returnvalue>"default"</returnvalue>
25413+
</para>
25414+
<para>
25415+
<literal>collation for ('foo' COLLATE "de_DE")</literal>
25416+
<returnvalue>"de_DE"</returnvalue>
2543325417
</para></entry>
2543425418
</row>
2543525419

@@ -25570,19 +25554,50 @@ SELECT collation for ('foo' COLLATE "de_DE");
2557025554
</row>
2557125555

2557225556
<row>
25573-
<entry role="func_table_entry"><para role="func_signature">
25557+
<entry id="to-regtype" xreflabel="to_regtype" role="func_table_entry"><para role="func_signature">
2557425558
<indexterm>
2557525559
<primary>to_regtype</primary>
2557625560
</indexterm>
2557725561
<function>to_regtype</function> ( <type>text</type> )
2557825562
<returnvalue>regtype</returnvalue>
2557925563
</para>
2558025564
<para>
25581-
Translates a textual type name to its OID. A similar result is
25582-
obtained by casting the string to type <type>regtype</type> (see
25583-
<xref linkend="datatype-oid"/>); however, this function will return
25584-
<literal>NULL</literal> rather than throwing an error if the name is
25585-
not found.
25565+
Parses a string of text, extracts a potential type name from it,
25566+
and translates that name into a type OID. A syntax error in the
25567+
string will result in an error; but if the string is a
25568+
syntactically valid type name that happens not to be found in the
25569+
catalogs, the result is <literal>NULL</literal>. A similar result
25570+
is obtained by casting the string to type <type>regtype</type>
25571+
(see <xref linkend="datatype-oid"/>), except that that will throw
25572+
error for name not found.
25573+
</para></entry>
25574+
</row>
25575+
25576+
<row>
25577+
<entry role="func_table_entry"><para role="func_signature">
25578+
<indexterm>
25579+
<primary>to_regtypemod</primary>
25580+
</indexterm>
25581+
<function>to_regtypemod</function> ( <type>text</type> )
25582+
<returnvalue>integer</returnvalue>
25583+
</para>
25584+
<para>
25585+
Parses a string of text, extracts a potential type name from it,
25586+
and translates its type modifier, if any. A syntax error in the
25587+
string will result in an error; but if the string is a
25588+
syntactically valid type name that happens not to be found in the
25589+
catalogs, the result is <literal>NULL</literal>. The result is
25590+
<literal>-1</literal> if no type modifier is present.
25591+
</para>
25592+
<para>
25593+
<function>to_regtypemod</function> can be combined with
25594+
<xref linkend="to-regtype"/> to produce appropriate inputs for
25595+
<xref linkend="format-type"/>, allowing a string representing a
25596+
type name to be canonicalized.
25597+
</para>
25598+
<para>
25599+
<literal>format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'))</literal>
25600+
<returnvalue>character varying(32)</returnvalue>
2558625601
</para></entry>
2558725602
</row>
2558825603
</tbody>

‎src/backend/utils/adt/regproc.c

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1220,6 +1220,26 @@ to_regtype(PG_FUNCTION_ARGS)
12201220
PG_RETURN_DATUM(result);
12211221
}
12221222

1223+
/*
1224+
* to_regtypemod - converts "typename" to type modifier
1225+
*
1226+
* If the name is not found, we return NULL.
1227+
*/
1228+
Datum
1229+
to_regtypemod(PG_FUNCTION_ARGS)
1230+
{
1231+
char *typ_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
1232+
Oid typid;
1233+
int32 typmod;
1234+
ErrorSaveContext escontext = {T_ErrorSaveContext};
1235+
1236+
/* We rely on parseTypeString to parse the input. */
1237+
if (!parseTypeString(typ_name, &typid, &typmod, (Node *) &escontext))
1238+
PG_RETURN_NULL();
1239+
1240+
PG_RETURN_INT32(typmod);
1241+
}
1242+
12231243
/*
12241244
* regtypeout - converts type OID to "typ_name"
12251245
*/

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202403201
60+
#define CATALOG_VERSION_NO 202403202
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7155,6 +7155,9 @@
71557155
{ oid => '3493', descr => 'convert type name to regtype',
71567156
proname => 'to_regtype', provolatile => 's', prorettype => 'regtype',
71577157
proargtypes => 'text', prosrc => 'to_regtype' },
7158+
{ oid => '8401', descr => 'convert type name to type modifier',
7159+
proname => 'to_regtypemod', provolatile => 's', prorettype => 'int4',
7160+
proargtypes => 'text', prosrc => 'to_regtypemod' },
71587161
{ oid => '1079', descr => 'convert text to regclass',
71597162
proname => 'regclass', provolatile => 's', prorettype => 'regclass',
71607163
proargtypes => 'text', prosrc => 'text_regclass' },

‎src/test/regress/expected/regproc.out

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -447,6 +447,43 @@ SELECT to_regnamespace('foo.bar');
447447

448448
(1 row)
449449

450+
-- Test to_regtypemod
451+
SELECT to_regtypemod('text');
452+
to_regtypemod
453+
---------------
454+
-1
455+
(1 row)
456+
457+
SELECT to_regtypemod('timestamp(4)');
458+
to_regtypemod
459+
---------------
460+
4
461+
(1 row)
462+
463+
SELECT to_regtypemod('no_such_type(4)');
464+
to_regtypemod
465+
---------------
466+
467+
(1 row)
468+
469+
SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
470+
format_type
471+
-----------------------
472+
character varying(32)
473+
(1 row)
474+
475+
SELECT format_type(to_regtype('bit'), to_regtypemod('bit'));
476+
format_type
477+
-------------
478+
bit(1)
479+
(1 row)
480+
481+
SELECT format_type(to_regtype('"bit"'), to_regtypemod('"bit"'));
482+
format_type
483+
-------------
484+
"bit"
485+
(1 row)
486+
450487
-- Test soft-error API
451488
SELECT * FROM pg_input_error_info('ng_catalog.pg_class', 'regclass');
452489
message | detail | hint | sql_error_code

‎src/test/regress/sql/regproc.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,14 @@ SELECT to_regnamespace('Nonexistent');
123123
SELECT to_regnamespace('"Nonexistent"');
124124
SELECT to_regnamespace('foo.bar');
125125

126+
-- Test to_regtypemod
127+
SELECT to_regtypemod('text');
128+
SELECT to_regtypemod('timestamp(4)');
129+
SELECT to_regtypemod('no_such_type(4)');
130+
SELECT format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'));
131+
SELECT format_type(to_regtype('bit'), to_regtypemod('bit'));
132+
SELECT format_type(to_regtype('"bit"'), to_regtypemod('"bit"'));
133+
126134
-- Test soft-error API
127135

128136
SELECT * FROM pg_input_error_info('ng_catalog.pg_class', 'regclass');

0 commit comments

Comments
 (0)