public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: [email protected]
Subject: Patch: Add parse_type Function
Date: Sun, 4 Feb 2024 12:51:29 -0500
Message-ID: <[email protected]> (raw)
Hackers,
Attached is a patch to add a new function, `parse_type()`. It parses a type string and returns a record with the typid and typmod for the type, or raises an error if the type is invalid. It’s effectively a thin layer over the parser’s parseTypeString() function.
The purpose of this function is to allow uses to resolve any type name, including aliases, to its formal name as rendered by, for example, pg_dump. An example:
david=# WITH s(s) AS (
SELECT * FROM unnest(ARRAY[
'timestamp(4)',
'interval(0)',
'interval second(0)',
'timestamptz',
'timestamptz(6)',
'varchar',
'varchar(128)'
])
),
p(type, typid, typmod) AS (
SELECT s, ((parse_type(s)).*)
FROM s
)
SELECT type, format_type(typid, typmod) FROM p;
type | format_type
--------------------+--------------------------------
timestamp(4) | timestamp(4) without time zone
interval(0) | interval(0)
interval second(0) | interval second(0)
timestamptz | timestamp with time zone
timestamptz(6) | timestamp(6) with time zone
varchar | character varying
varchar(128) | character varying(128)
(7 rows)
The use case leading to this patch was pgTAP column data type assertions. pgTAP traditionally required full type names for testing data types, but recently added support for aliases. This broke for some types, because it was difficult to extract the typmod correctly, and even when we did, it failed for types such as `interval second(0)`, where `second (0)` is the typmod, and there was no sensible way to access the bit mask to generate the proper typmod to pass to `format_type()`.
Erik Wienhold wrote this function to solve the problem, and I volunteered to submit a patch.
Potential issues and questions for this patch:
* Is `parse_type()` the right name to use? I can see arguments for `parse_type_string()`, `pg_parse_type()`, and `pg_parse_type_string()`. Whatever the consensus is works for me.
* The function returns a record, which is a little fussy. I could see adding a `format_type_string()` function that just contains `SELECT format_type(p.typmod, p.typid) FROM parse_type($1) p` and returns the formatted type. But I think it might also be useful to have access to the typmod and typid directly via this method, since they’re already exposed as `format_type()`’s arguments.
* Is format_type.c the right home for the function? I put it there because I closely associate it with format_type(), but happy to move it to a more appropriate location.
* I tried to link to `format_type` from the docs entry for `parse_type`, and added an ID for the former, but I’m not sure it resolves.
Best,
David
Attachments:
[application/octet-stream] v1-0001-Add-parse_type-SQL-function.patch (10.3K, ../[email protected]/2-v1-0001-Add-parse_type-SQL-function.patch)
download | inline diff:
From b426a1a258b65237632039abffb1ab101ce709c9 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Sun, 4 Feb 2024 12:49:18 -0500
Subject: [PATCH v1] 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 | 66 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 ++
src/include/utils/builtins.h | 2 +
src/test/regress/expected/create_type.out | 47 ++++++++++++++++
src/test/regress/sql/create_type.sql | 27 ++++++++++
6 files changed, 176 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8ef4..12e2d69568 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>typid</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..2477c8b8e0 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,66 @@ 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};
+
+ 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..5a3ebfc658 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -249,6 +249,53 @@ 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)
+
+-- 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..c14c7590a9 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -192,6 +192,33 @@ 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);
+
+-- 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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Patch: Add parse_type Function
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox