Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ra1mv-007jhI-A6 for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Feb 2024 23:02:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ra1mt-002reN-W5 for pgsql-hackers@arkaria.postgresql.org; Tue, 13 Feb 2024 23:02:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ra1mt-002reF-3i for pgsql-hackers@lists.postgresql.org; Tue, 13 Feb 2024 23:02:03 +0000 Received: from pb-smtp2.pobox.com ([64.147.108.71]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ra1mq-006c5C-De for pgsql-hackers@lists.postgresql.org; Tue, 13 Feb 2024 23:02:01 +0000 Received: from pb-smtp2.pobox.com (unknown [127.0.0.1]) by pb-smtp2.pobox.com (Postfix) with ESMTP id 787421CA594; Tue, 13 Feb 2024 18:01:58 -0500 (EST) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=pobox.com; h=from :message-id:content-type:mime-version:subject:date:in-reply-to :cc:to:references; s=sasl; bh=5MOIE68bZAO2ZvK5cs1BSz5Qfsw2Q/9jXA JdbolRlRk=; b=lkeOmRVY0iSHMrjwt5oVPZxPsNKLloYMroJ5+jpU+caUW+5kC3 a67eWxlQte3PyLJNUMHR35MacUDblxpI6lyd3IXNRML17/Bva8Y24TR3EOknLBLO fRYy5YWkuGlMUYAhNSZ6D+5b3DEsUt/uTDtBDnKy3w38IAcQyNCQHHpM0= Received: from pb-smtp2.nyi.icgroup.com (unknown [127.0.0.1]) by pb-smtp2.pobox.com (Postfix) with ESMTP id 6F6331CA592; Tue, 13 Feb 2024 18:01:58 -0500 (EST) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=justatheory.com; h=from:message-id:content-type:mime-version:subject:date:in-reply-to:cc:to:references; s=2016-12.pbsmtp; bh=5MOIE68bZAO2ZvK5cs1BSz5Qfsw2Q/9jXAJdbolRlRk=; b=Wp1LJ1CAFlIwRzDYNJGpejqpt0EEeLd5DsS0db2cH6D7/XSg82kK1yXxry/IQVfcksIpfGqvvwCe9o/411y+zWUYWFNOPDScAwgsbkWhdSNAHiO7VzUwFQngjXc4t2RX29n7RHdHASIvgf3kcZGq7ALqPUTEq+iqz8h9ImVNV7o= Received: from smtpclient.apple (unknown [158.222.197.125]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by pb-smtp2.pobox.com (Postfix) with ESMTPSA id 7B1FC1CA591; Tue, 13 Feb 2024 18:01:57 -0500 (EST) (envelope-from david@justatheory.com) From: "David E. Wheeler" Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_5E746CD6-90E5-40A5-8DBE-B16C8CA38604" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Patch: Add parse_type Function Date: Tue, 13 Feb 2024 18:01:46 -0500 In-Reply-To: Cc: Erik Wienhold , jian he , Jim Jones , =?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= , pgsql-hackers@lists.postgresql.org, Pavel Stehule To: Tom Lane References: <87il33kmmn.fsf@wibble.ilmari.org> <505981B3-2572-41E0-896C-964DDF7048A9@justatheory.com> <1317430675.73641.1707616376477@office.mailbox.org> <2245610.1707760389@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3774.300.61.1.2) X-Pobox-Relay-ID: E3660596-CAC3-11EE-81BA-25B3960A682E-76319746!pb-smtp2.pobox.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5E746CD6-90E5-40A5-8DBE-B16C8CA38604 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii On Feb 12, 2024, at 15:55, David E. Wheeler = 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.=20 Best, David --Apple-Mail=_5E746CD6-90E5-40A5-8DBE-B16C8CA38604 Content-Disposition: attachment; filename=v5-0001-Add-parse_type-SQL-function.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v5-0001-Add-parse_type-SQL-function.patch" Content-Transfer-Encoding: quoted-printable =46rom=20857f7eace0bc65ef882dd8ced51993e37142055c=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"David=20E.=20Wheeler"=20= =0ADate:=20Tue,=2013=20Feb=202024=2017:58:34=20= -0500=0ASubject:=20[PATCH=20v5]=20Add=20parse_type()=20SQL=20function=0A=0A= The=20`parse_type()`=20function=20uses=20the=20underlying=20= `parseTypeString()`=20C=0Afunction=20to=20parse=20a=20string=20= representing=20a=20data=20type=20into=20a=20type=20ID=20and=0Atypmod=20= suitable=20for=20passing=20to=20`format_type()`.=20This=20allows=20one=20= to=0Aderive=20the=20formal=20SQL=20name=20for=20a=20type=20from=20a=20= string=20that=20may=20be=20an=0Aalias:=0A=0A=20=20=20=20SELECT=20= format_type(p.typid,=20p.typmod)=0A=20=20=20=20=20=20FROM=20= parse_type('timestamp(4)')=20p;=0A=0A=20=20=20=20=20=20=20=20=20=20=20=20= format_type=0A=20=20=20=20--------------------------------=0A=20=20=20=20= timestamp(4)=20without=20time=20zone=0A=0AThis=20function=20also=20= resolves=20types=20whose=20typmod=20is=20determined=20by=20the=20SQL=0A= parser=20or=20some=20step=20after=20that,=20such=20as=20interval=20types=20= where=20the=20stored=0Afield=20option=20is=20encoded=20in=20the=20= typmod:=0A=0A=20=20=20=20SELECT=20format_type(p.typid,=20p.typmod)=0A=20=20= =20=20=20=20FROM=20parse_type('interval=20second(0)')=20p;=0A=0A=20=20=20= =20=20=20=20=20format_type=0A=20=20=20=20--------------------=0A=20=20=20= =20interval=20second(0)=0A=0AUseful=20for=20unit=20tests=20for=20against=20= column=20data=20types,=20for=20example.=0AOriginally=20written=20by=20= Erik=20Wienhold=20for=20use=20in=20pgTAP.=0A---=0A=20= doc/src/sgml/func.sgml=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20|=20= 30=20+++++++++++++++-=0A=20src/backend/utils/adt/regproc.c=20=20=20=20=20= =20=20|=2051=20+++++++++++++++++++++++++++=0A=20= src/include/catalog/pg_proc.dat=20=20=20=20=20=20=20|=20=206=20++++=0A=20= src/test/regress/expected/regproc.out=20|=2051=20= +++++++++++++++++++++++++++=0A=20src/test/regress/sql/regproc.sql=20=20=20= =20=20=20|=2028=20+++++++++++++++=0A=205=20files=20changed,=20165=20= insertions(+),=201=20deletion(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/func.sgml=20b/doc/src/sgml/func.sgml=0Aindex=20= 11d537b341..adcc433a69=20100644=0A---=20a/doc/src/sgml/func.sgml=0A+++=20= b/doc/src/sgml/func.sgml=0A@@=20-24759,7=20+24759,7=20@@=20SELECT=20= pg_type_is_visible('myschema.widget'::regtype);=0A=20=0A=20=20=20=20=20=20= =0A=20=20=20=20=20=20=20=0A-=20=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=0A=20=20=20=20=20=20=20=20=20=0A=20=20= =20=20=20=20=20=20=20=20format_type=0A=20=20=20=20=20=20= =20=20=20=0A@@=20-24773,6=20+24773,34=20@@=20SELECT=20= pg_type_is_visible('myschema.widget'::regtype);=0A=20=20=20=20=20=20=20=20= =0A=20=20=20=20=20=20=20=0A=20=0A+=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=20=0A+=20=20=20=20=20=20=20=20=20= parse_type=0A+=20=20=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=20=20parse_type=20(=20= type=20text=20)=0A+=20=20=20=20=20=20= =20=20record=0A+=20=20=20=20=20=20=20=20(=20= typid=20oid,=0A+=20=20=20=20=20=20=20= =20=20=20typmod=20integer=20)=0A+=20=20= =20=20=20=20=20=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20= =20=20Parses=20a=20string=20representing=20an=20SQL=20data=20type,=20= optionally=20schema-qualified.=0A+=20=20=20=20=20=20=20=20Returns=20a=20= record=20with=20two=20fields,=20typid=20and=0A+=20= =20=20=20=20=20=20=20typmod,=20representing=20the=20= OID=20and=20modifier=20for=20the=0A+=20=20=20=20=20=20=20=20type.=20= These=20correspond=20to=20the=20parameters=20to=20pass=20to=20the=0A+=20=20= =20=20=20=20=20=20format_type=20= function.=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=20For=20example:=0A+=0A= +SELECT=20format_type(p.typid,=20p.typmod)=20FROM=20= parse_type('timestamp(4)')=20p;=0A+=20=20=20=20=20=20=20=20format_type=0A= +=20--------------------------------=0A+=20=20timestamp(4)=20without=20= time=20zone=0A+=0A+=20=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=0A+=0A=20=20=20=20=20=20=20=0A=20=20=20=20= =20=20=20=20=0A=20=20=20=20= =20=20=20=20=20=0Adiff=20--git=20= a/src/backend/utils/adt/regproc.c=20b/src/backend/utils/adt/regproc.c=0A= index=201e3bf3f5fd..4fee27a139=20100644=0A---=20= a/src/backend/utils/adt/regproc.c=0A+++=20= b/src/backend/utils/adt/regproc.c=0A@@=20-30,6=20+30,7=20@@=0A=20= #include=20"catalog/pg_ts_config.h"=0A=20#include=20= "catalog/pg_ts_dict.h"=0A=20#include=20"catalog/pg_type.h"=0A+#include=20= "funcapi.h"=0A=20#include=20"lib/stringinfo.h"=0A=20#include=20= "mb/pg_wchar.h"=0A=20#include=20"miscadmin.h"=0A@@=20-2016,3=20+2017,53=20= @@=20parseNameAndArgTypes(const=20char=20*string,=20bool=20allowNone,=20= List=20**names,=0A=20=0A=20=09return=20true;=0A=20}=0A+=0A+=0A+/*=0A+=20= *=20parse_type()=20is=20the=20inverse=20of=20pg_catalog.format_type():=20= it=20takes=20a=20string=0A+=20*=20representing=20an=20SQL-compatible=20= type=20declaration,=20such=20as=20"int4"=20or=20"integer"=0A+=20*=20or=20= "character=20varying(32)",=20parses=20it,=20and=20returns=20the=20OID=20= and=20type=20modifier.=0A+=20*=0A+=20*=20Raises=20an=20error=20on=20an=20= invalid=20type.=0A+=20*=0A+=20*=20Internally=20it=20relies=20on=20the=20= Postgres=20core=20parseTypeString()=20function=20defined=0A+=20*=20in=20= src/backend/parser/parse_type.c.=0A+=20*/=0A+Datum=0A= +parse_type(PG_FUNCTION_ARGS)=0A+{=0A+#define=20PARSE_TYPE_STRING_COLS=20= 2=20/*=20Returns=20two=20columns.=20*/=0A+=09const=20char=20*type;=20=20=20= =20=20=20=20=20=20=20=20=20/*=20the=20type=20string=20we=20want=20to=20= resolve=20*/=0A+=09Oid=20=20=20=20=20=20=20=20=20typid;=20=20=20=20=20=20= =20=20=20=20=20/*=20the=20resolved=20type=20oid=20*/=0A+=09int32=20=20=20= =20=20=20=20typmod;=20=20=20=20=20=20=20=20=20=20/*=20the=20resolved=20= type=20modifier=20*/=0A+=09TupleDesc=20=20=20tupdesc;=0A+=09HeapTuple=20=20= =20rettuple;=0A+=09Datum=20=20=20=20=20=20=20= values[PARSE_TYPE_STRING_COLS]=20=3D=20{0};=0A+=09bool=20=20=20=20=20=20=20= =20nulls[PARSE_TYPE_STRING_COLS]=20=3D=20{0};=0A+=0A+=09type=20=3D=20= text_to_cstring(PG_GETARG_TEXT_PP(0));=0A+=0A+=09/*=0A+=09=20*=20Build=20= a=20tuple=20descriptor=20for=20our=20result=20type;=20return=20an=20= error=20if=20not=0A+=09=20*=20called=20in=20a=20context=20that=20expects=20= a=20record.=0A+=09=20*/=0A+=09if=20(get_call_result_type(fcinfo,=20NULL,=20= &tupdesc)=20!=3D=20TYPEFUNC_COMPOSITE)=0A+=09=09ereport(ERROR,=0A+=09=09=09= =09(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),=0A+=09=09=09=09= errmsg("function=20returning=20record=20called=20in=20context=20that=20= cannot=20accept=20type=20record")));=0A+=0A+=09BlessTupleDesc(tupdesc);=0A= +=0A+=09/*=0A+=09=20*=20Parse=20type-name=20argument=20to=20obtain=20= type=20OID=20and=20encoded=20typmod.=20We=20don't=0A+=09=20*=20need=20to=20= handle=20parseTypeString=20failure,=20just=20let=20the=20error=20be=20= raised.=0A+=09=20*/=0A+=09(void)=20parseTypeString(type,=20&typid,=20= &typmod,=20NULL);=0A+=0A+=09/*=20Create=20and=20return=20tuple.=20*/=0A+=09= values[0]=20=3D=20typid;=0A+=09values[1]=20=3D=20typmod;=0A+=09rettuple=20= =3D=20heap_form_tuple(tupdesc,=20values,=20nulls);=0A+=09return=20= HeapTupleGetDatum(rettuple);=0A+#undef=20PARSE_TYPE_STRING_COLS=0A+}=0A= diff=20--git=20a/src/include/catalog/pg_proc.dat=20= b/src/include/catalog/pg_proc.dat=0Aindex=2029af4ce65d..8071b44f6b=20= 100644=0A---=20a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-2184,6=20+2184,12=20@@=0A=20{=20= oid=20=3D>=20'1081',=20descr=20=3D>=20'format=20a=20type=20oid=20and=20= atttypmod=20to=20canonical=20SQL',=0A=20=20=20proname=20=3D>=20= 'format_type',=20proisstrict=20=3D>=20'f',=20provolatile=20=3D>=20's',=0A= =20=20=20prorettype=20=3D>=20'text',=20proargtypes=20=3D>=20'oid=20= int4',=20prosrc=20=3D>=20'format_type'=20},=0A+{=20oid=20=3D>=20'8401',=0A= +=20=20descr=20=3D>=20'parse=20a=20type=20string=20into=20its=20a=20type=20= oid=20and=20atttypmod',=0A+=20=20proname=20=3D>=20'parse_type',=20= provolatile=20=3D>=20's',=20prorettype=20=3D>=20'record',=0A+=20=20= proargtypes=20=3D>=20'text',=20proallargtypes=20=3D>=20= '{text,oid,int4}',=0A+=20=20proargmodes=20=3D>=20'{i,o,o}',=20= proargnames=20=3D>=20'{typname,typid,typmod}',=0A+=20=20prosrc=20=3D>=20= 'parse_type'=20},=0A=20{=20oid=20=3D>=20'1084',=20descr=20=3D>=20'I/O',=0A= =20=20=20proname=20=3D>=20'date_in',=20provolatile=20=3D>=20's',=20= prorettype=20=3D>=20'date',=0A=20=20=20proargtypes=20=3D>=20'cstring',=20= prosrc=20=3D>=20'date_in'=20},=0Adiff=20--git=20= a/src/test/regress/expected/regproc.out=20= b/src/test/regress/expected/regproc.out=0Aindex=20a9420850b8..033f6a81a5=20= 100644=0A---=20a/src/test/regress/expected/regproc.out=0A+++=20= b/src/test/regress/expected/regproc.out=0A@@=20-544,3=20+544,54=20@@=20= SELECT=20*=20FROM=20pg_input_error_info('way.too.many.names',=20= 'regtype');=0A=20ERROR:=20=20improper=20qualified=20name=20(too=20many=20= dotted=20names):=20way.too.many.names=0A=20SELECT=20*=20FROM=20= pg_input_error_info('no_such_catalog.schema.name',=20'regtype');=0A=20= ERROR:=20=20cross-database=20references=20are=20not=20implemented:=20= no_such_catalog.schema.name=0A+--=20Test=20parse_type=0A+SELECT=20*=20= FROM=20parse_type('text')=20p(typid,=20typmod);=0A+=20typid=20|=20typmod=20= =0A+-------+--------=0A+=20=20=20=2025=20|=20=20=20=20=20-1=0A+(1=20row)=0A= +=0A+SELECT=20*=20FROM=20parse_type(NULL)=20p(typid,=20typmod);=0A+=20= typid=20|=20typmod=20=0A+-------+--------=0A+=20=20=20=20=20=20=20|=20=20= =20=20=20=20=20=0A+(1=20row)=0A+=0A+--=20Test=20parse_type=20errors=0A= +SELECT=20parse_type('nonesuch');=20--=20error=20expected=0A+ERROR:=20=20= type=20"nonesuch"=20does=20not=20exist=0A+SELECT=20parse_type('interval=20= nonesuch');=20--=20grammar=20error=20expected=0A+ERROR:=20=20syntax=20= error=20at=20or=20near=20"nonesuch"=0A+LINE=201:=20SELECT=20= parse_type('interval=20nonesuch');=0A+=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20^=0A+CONTEXT:=20=20invalid=20type=20name=20"interval=20= nonesuch"=0A+SELECT=20parse_type('year(4)');=20--=20grammar=20error=20= expected=0A+ERROR:=20=20type=20"year"=20does=20not=20exist=0A+--=20Test=20= parse_type=20with=20various=20aliases=20and=20grammar-based=20types=0A= +WITH=20s(s)=20AS=20(=0A+=20=20=20=20SELECT=20*=20FROM=20unnest(ARRAY[=0A= +=20=20=20=20=20=20=20=20'timestamp(4)',=0A+=20=20=20=20=20=20=20=20= 'interval(0)',=0A+=20=20=20=20=20=20=20=20'interval=20second(0)',=0A+=20=20= =20=20=20=20=20=20'timestamptz',=0A+=20=20=20=20=20=20=20=20= 'timestamptz(6)',=0A+=20=20=20=20=20=20=20=20'varchar',=0A+=20=20=20=20=20= =20=20=20'varchar(128)'=0A+=20=20=20=20])=0A+),=0A+p(typid,=20typmod)=20= AS=20(=0A+=20=20=20=20SELECT=20((parse_type(s)).*)=0A+=20=20=20=20=20=20= FROM=20s=0A+)=0A+SELECT=20format_type(typid,=20typmod)=20FROM=20p;=0A+=20= =20=20=20=20=20=20=20=20=20format_type=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------=0A+=20timestamp(4)=20without=20time=20= zone=0A+=20interval(0)=0A+=20interval=20second(0)=0A+=20timestamp=20with=20= time=20zone=0A+=20timestamp(6)=20with=20time=20zone=0A+=20character=20= varying=0A+=20character=20varying(128)=0A+(7=20rows)=0A+=0Adiff=20--git=20= a/src/test/regress/sql/regproc.sql=20b/src/test/regress/sql/regproc.sql=0A= index=20de2aa881a8..489463aa9e=20100644=0A---=20= a/src/test/regress/sql/regproc.sql=0A+++=20= b/src/test/regress/sql/regproc.sql=0A@@=20-145,3=20+145,31=20@@=20SELECT=20= *=20FROM=20pg_input_error_info('incorrect=20type=20name=20syntax',=20= 'regtype');=0A=20SELECT=20*=20FROM=20= pg_input_error_info('numeric(1,2,3)',=20'regtype');=20=20--=20bogus=20= typmod=0A=20SELECT=20*=20FROM=20= pg_input_error_info('way.too.many.names',=20'regtype');=0A=20SELECT=20*=20= FROM=20pg_input_error_info('no_such_catalog.schema.name',=20'regtype');=0A= +=0A+--=20Test=20parse_type=0A+SELECT=20*=20FROM=20parse_type('text')=20= p(typid,=20typmod);=0A+SELECT=20*=20FROM=20parse_type(NULL)=20p(typid,=20= typmod);=0A+=0A+--=20Test=20parse_type=20errors=0A+SELECT=20= parse_type('nonesuch');=20--=20error=20expected=0A+SELECT=20= parse_type('interval=20nonesuch');=20--=20grammar=20error=20expected=0A= +SELECT=20parse_type('year(4)');=20--=20grammar=20error=20expected=0A+=0A= +--=20Test=20parse_type=20with=20various=20aliases=20and=20grammar-based=20= types=0A+WITH=20s(s)=20AS=20(=0A+=20=20=20=20SELECT=20*=20FROM=20= unnest(ARRAY[=0A+=20=20=20=20=20=20=20=20'timestamp(4)',=0A+=20=20=20=20=20= =20=20=20'interval(0)',=0A+=20=20=20=20=20=20=20=20'interval=20= second(0)',=0A+=20=20=20=20=20=20=20=20'timestamptz',=0A+=20=20=20=20=20=20= =20=20'timestamptz(6)',=0A+=20=20=20=20=20=20=20=20'varchar',=0A+=20=20=20= =20=20=20=20=20'varchar(128)'=0A+=20=20=20=20])=0A+),=0A+p(typid,=20= typmod)=20AS=20(=0A+=20=20=20=20SELECT=20((parse_type(s)).*)=0A+=20=20=20= =20=20=20FROM=20s=0A+)=0A+SELECT=20format_type(typid,=20typmod)=20FROM=20= p;=0A+=0A--=20=0A2.43.1=0A=0A= --Apple-Mail=_5E746CD6-90E5-40A5-8DBE-B16C8CA38604--