public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: Tom Lane <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: jian he <[email protected]>
Cc: Jim Jones <[email protected]>
Cc: Dagfinn Ilmari Mannsåker <[email protected]>
Cc: [email protected]
Cc: Pavel Stehule <[email protected]>
Subject: Re: Patch: Add parse_type Function
Date: Tue, 13 Feb 2024 18:01:46 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxHS2ULcDvZJagS=RVo=Hqq_TC-jOG0euVdKvJ-DkcKtuw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Feb 12, 2024, at 15:55, David E. Wheeler <[email protected]> wrote:
> Happy to move it wherever makes the most sense.
Update with a bunch of the suggested changes. Some questions still open from the previous post, though.
Best,
David
Attachments:
[application/octet-stream] v5-0001-Add-parse_type-SQL-function.patch (9.5K, ../[email protected]/2-v5-0001-Add-parse_type-SQL-function.patch)
download | inline diff:
From 857f7eace0bc65ef882dd8ced51993e37142055c Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
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
view thread (2+ messages)
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], [email protected], [email protected], [email protected], [email protected], [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