I want to address the issue that calling a record-returning function
always requires specifying a result column list, even though there are
cases where the function could be self-aware enough to know the result
column list of a particular call. For example, most of the functions in
contrib/tablefunc are like that.
SQL:2016 has a feature called polymorphic table functions (PTF) that
addresses this. The full PTF feature is much larger, so I just carved
out this particular piece of functionality. Here is a link to some
related information:
https://modern-sql.com/blog/2018-11/whats-new-in-oracle-database-18c#ptf
The idea is that you attach a helper function to the main function. The
helper function is called at parse time with the constant arguments of
the main function call and can compute a result row description (a
TupleDesc in our case).
Example from the patch:
CREATE FUNCTION connectby_describe(internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'connectby_describe'
LANGUAGE C;
CREATE FUNCTION connectby(text,text,text,text,int,text)
RETURNS setof record
DESCRIBE WITH connectby_describe
AS 'MODULE_PATHNAME','connectby_text'
LANGUAGE C STABLE STRICT;
(The general idea is very similar to Pavel's patch "parse time support
function"[0] but addressing a disjoint problem.)
The original SQL:2016 syntax is a bit different: There, you'd first
create two separate functions: a "describe" and a "fulfill" and then
create the callable PTF referencing those two (similar to how an
aggregate is composed of several component functions). I think
deviating from this makes some sense because we can then more easily
"upgrade" existing record-returning functions with this functionality.
Another difference is that AFAICT, the standard specifies that if the
describe function cannot resolve the call, the call fails. Again, in
order to be able to upgrade existing functions (instead of having to
create a second set of functions with a different name), I have made it
so that you can still specify an explicit column list if the describe
function does not succeed.
In this prototype patch, I have written the C interface and several
examples using existing functions in the source tree. Eventually, I'd
like to also add PL-level support for this.
Thoughts so far?
[0]:
https://www.postgresql.org/message-id/flat/CAFj8pRARh+r4=hnwq+hws-d6msus01dw_6zjnyur6tpk1+w...@mail.gmail.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From c517222f5de87961e0cebd91efc5ebfea7737888 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 16 Dec 2019 18:49:10 +0100
Subject: [PATCH v1] Polymorphic table functions
---
contrib/tablefunc/expected/tablefunc.out | 46 +++++++++++++++
contrib/tablefunc/sql/tablefunc.sql | 8 +++
contrib/tablefunc/tablefunc--1.0.sql | 7 +++
contrib/tablefunc/tablefunc.c | 69 ++++++++++++++++++++++
contrib/xml2/expected/xml2.out | 10 ++++
contrib/xml2/sql/xml2.sql | 6 ++
contrib/xml2/xml2--1.1.sql | 6 ++
contrib/xml2/xpath.c | 72 +++++++++++++++++++++++
doc/src/sgml/catalogs.sgml | 12 ++++
doc/src/sgml/queries.sgml | 8 +++
doc/src/sgml/ref/create_function.sgml | 14 +++++
doc/src/sgml/xfunc.sgml | 66 +++++++++++++++++++++
src/backend/catalog/pg_aggregate.c | 1 +
src/backend/catalog/pg_proc.c | 11 ++++
src/backend/commands/functioncmds.c | 30 +++++++++-
src/backend/commands/proclang.c | 3 +
src/backend/commands/typecmds.c | 1 +
src/backend/executor/execSRF.c | 1 +
src/backend/executor/nodeFunctionscan.c | 1 +
src/backend/optimizer/prep/prepjointree.c | 1 +
src/backend/parser/gram.y | 7 ++-
src/backend/parser/parse_relation.c | 3 +
src/backend/utils/adt/jsonfuncs.c | 48 +++++++++++++++
src/backend/utils/fmgr/funcapi.c | 49 ++++++++++++++-
src/include/catalog/pg_class.dat | 2 +-
src/include/catalog/pg_proc.dat | 6 +-
src/include/catalog/pg_proc.h | 4 ++
src/include/funcapi.h | 1 +
src/include/parser/kwlist.h | 1 +
src/interfaces/ecpg/preproc/ecpg.tokens | 2 +-
src/interfaces/ecpg/preproc/ecpg.trailer | 11 ++--
src/interfaces/ecpg/preproc/ecpg_kwlist.h | 1 -
src/test/regress/expected/json.out | 6 ++
src/test/regress/sql/json.sql | 2 +
34 files changed, 503 insertions(+), 13 deletions(-)
diff --git a/contrib/tablefunc/expected/tablefunc.out
b/contrib/tablefunc/expected/tablefunc.out
index fffadc6e1b..485ddfba87 100644
--- a/contrib/tablefunc/expected/tablefunc.out
+++ b/contrib/tablefunc/expected/tablefunc.out
@@ -328,6 +328,29 @@ SELECT * FROM connectby('connectby_text', 'keyid',
'parent_keyid', 'pos', 'row2'
row8 | row6 | 3 | 6
(6 rows)
+-- PTF
+SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0,
'~');
+ keyid | parent_keyid | level | branch
+-------+--------------+-------+---------------------
+ row2 | | 0 | row2
+ row4 | row2 | 1 | row2~row4
+ row6 | row4 | 2 | row2~row4~row6
+ row8 | row6 | 3 | row2~row4~row6~row8
+ row5 | row2 | 1 | row2~row5
+ row9 | row5 | 2 | row2~row5~row9
+(6 rows)
+
+SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0);
+ keyid | parent_keyid | level
+-------+--------------+-------
+ row2 | | 0
+ row4 | row2 | 1
+ row6 | row4 | 2
+ row8 | row6 | 3
+ row5 | row2 | 1
+ row9 | row5 | 2
+(6 rows)
+
-- test connectby with int based hierarchy
CREATE TABLE connectby_int(keyid int, parent_keyid int);
\copy connectby_int from 'data/connectby_int.data'
@@ -355,6 +378,29 @@ SELECT * FROM connectby('connectby_int', 'keyid',
'parent_keyid', '2', 0) AS t(k
9 | 5 | 2
(6 rows)
+-- PTF
+SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~');
+ keyid | parent_keyid | level | branch
+-------+--------------+-------+---------
+ 2 | | 0 | 2
+ 4 | 2 | 1 | 2~4
+ 6 | 4 | 2 | 2~4~6
+ 8 | 6 | 3 | 2~4~6~8
+ 5 | 2 | 1 | 2~5
+ 9 | 5 | 2 | 2~5~9
+(6 rows)
+
+SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0);
+ keyid | parent_keyid | level
+-------+--------------+-------
+ 2 | | 0
+ 4 | 2 | 1
+ 6 | 4 | 2
+ 8 | 6 | 3
+ 5 | 2 | 1
+ 9 | 5 | 2
+(6 rows)
+
-- recursion detection
INSERT INTO connectby_int VALUES(10,9);
INSERT INTO connectby_int VALUES(11,10);
diff --git a/contrib/tablefunc/sql/tablefunc.sql
b/contrib/tablefunc/sql/tablefunc.sql
index ec375b05c6..375f59bc7a 100644
--- a/contrib/tablefunc/sql/tablefunc.sql
+++ b/contrib/tablefunc/sql/tablefunc.sql
@@ -158,6 +158,10 @@ CREATE TABLE connectby_text(keyid text, parent_keyid text,
pos int);
-- without branch, with orderby
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos',
'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY
t.pos;
+-- PTF
+SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0,
'~');
+SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0);
+
-- test connectby with int based hierarchy
CREATE TABLE connectby_int(keyid int, parent_keyid int);
\copy connectby_int from 'data/connectby_int.data'
@@ -168,6 +172,10 @@ CREATE TABLE connectby_int(keyid int, parent_keyid int);
-- without branch
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS
t(keyid int, parent_keyid int, level int);
+-- PTF
+SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~');
+SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0);
+
-- recursion detection
INSERT INTO connectby_int VALUES(10,9);
INSERT INTO connectby_int VALUES(11,10);
diff --git a/contrib/tablefunc/tablefunc--1.0.sql
b/contrib/tablefunc/tablefunc--1.0.sql
index 8681ff4706..e75bdc0510 100644
--- a/contrib/tablefunc/tablefunc--1.0.sql
+++ b/contrib/tablefunc/tablefunc--1.0.sql
@@ -65,13 +65,20 @@ CREATE FUNCTION crosstab(text,text)
AS 'MODULE_PATHNAME','crosstab_hash'
LANGUAGE C STABLE STRICT;
+CREATE FUNCTION connectby_describe(internal)
+RETURNS internal
+AS 'MODULE_PATHNAME', 'connectby_describe'
+LANGUAGE C;
+
CREATE FUNCTION connectby(text,text,text,text,int,text)
RETURNS setof record
+DESCRIBE WITH connectby_describe
AS 'MODULE_PATHNAME','connectby_text'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION connectby(text,text,text,text,int)
RETURNS setof record
+DESCRIBE WITH connectby_describe
AS 'MODULE_PATHNAME','connectby_text'
LANGUAGE C STABLE STRICT;
diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c
index 256d52fc62..0d49ff1e1d 100644
--- a/contrib/tablefunc/tablefunc.c
+++ b/contrib/tablefunc/tablefunc.c
@@ -35,6 +35,7 @@
#include <math.h>
#include "access/htup_details.h"
+#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -42,6 +43,8 @@
#include "miscadmin.h"
#include "tablefunc.h"
#include "utils/builtins.h"
+#include "utils/regproc.h"
+#include "utils/lsyscache.h"
PG_MODULE_MAGIC;
@@ -1586,3 +1589,69 @@ compatCrosstabTupleDescs(TupleDesc ret_tupdesc,
TupleDesc sql_tupdesc)
/* OK, the two tupdescs are compatible for our purposes */
return true;
}
+
+PG_FUNCTION_INFO_V1(connectby_describe);
+
+Datum
+connectby_describe(PG_FUNCTION_ARGS)
+{
+ char *relname;
+ char *key_fld;
+ char *parent_key_fld;
+ bool show_branch;
+ TupleDesc tupdesc;
+ List *names;
+ Oid relid;
+ AttrNumber keyattnum;
+ Oid keytype;
+ int32 keytypmod;
+ Oid keycoll;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2))
+ PG_RETURN_NULL();
+
+ show_branch = (PG_NARGS() == 6);
+
+ if (show_branch && PG_ARGISNULL(5))
+ PG_RETURN_NULL();
+
+ relname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ key_fld = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ parent_key_fld = text_to_cstring(PG_GETARG_TEXT_PP(2));
+
+ tupdesc = CreateTemplateTupleDesc(3 + (show_branch ? 1 : 0));
+
+ names = stringToQualifiedNameList(relname);
+ relid = RangeVarGetRelid(makeRangeVarFromNameList(names),
AccessShareLock, false);
+
+ keyattnum = get_attnum(relid, key_fld);
+ if (keyattnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does
not exist",
+ key_fld, relname)));
+ get_atttypetypmodcoll(relid, keyattnum,
+ &keytype, &keytypmod,
&keycoll);
+ TupleDescInitEntry(tupdesc, 1, key_fld, keytype, keytypmod, 0);
+ TupleDescInitEntryCollation(tupdesc, 1, keycoll);
+
+ keyattnum = get_attnum(relid, parent_key_fld);
+ if (keyattnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does
not exist",
+ parent_key_fld, relname)));
+ get_atttypetypmodcoll(relid, keyattnum,
+ &keytype, &keytypmod,
&keycoll);
+ TupleDescInitEntry(tupdesc, 2, parent_key_fld, keytype, keytypmod, 0);
+ TupleDescInitEntryCollation(tupdesc, 2, keycoll);
+
+ TupleDescInitEntry(tupdesc, 3, "level", INT4OID, -1, 0);
+
+ if (show_branch)
+ TupleDescInitEntry(tupdesc, 4, "branch", TEXTOID, -1, 0);
+
+ BlessTupleDesc(tupdesc);
+
+ PG_RETURN_POINTER(tupdesc);
+}
diff --git a/contrib/xml2/expected/xml2.out b/contrib/xml2/expected/xml2.out
index eba6ae6036..1df04be6c4 100644
--- a/contrib/xml2/expected/xml2.out
+++ b/contrib/xml2/expected/xml2.out
@@ -88,6 +88,16 @@ as t(id int4, doc int4);
1 | 1
(1 row)
+-- PTF
+DROP TABLE xpath_test;
+CREATE TABLE xpath_test (id integer NOT NULL, t text);
+INSERT INTO xpath_test VALUES (1,
'<doc><data>foo</data><stuff>bar</stuff></doc>');
+SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/data|/doc/stuff',
'true');
+ id | t1 | t2
+----+-----+-----
+ 1 | foo | bar
+(1 row)
+
create table articles (article_id integer, article_xml xml, date_entered date);
insert into articles (article_id, article_xml, date_entered)
values (2, '<article><author>test</author><pages>37</pages></article>', now());
diff --git a/contrib/xml2/sql/xml2.sql b/contrib/xml2/sql/xml2.sql
index ac49cfa7c5..d1a9cb3494 100644
--- a/contrib/xml2/sql/xml2.sql
+++ b/contrib/xml2/sql/xml2.sql
@@ -34,6 +34,12 @@ CREATE TABLE xpath_test (id integer NOT NULL, t text);
SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int', 'true')
as t(id int4, doc int4);
+-- PTF
+DROP TABLE xpath_test;
+CREATE TABLE xpath_test (id integer NOT NULL, t text);
+INSERT INTO xpath_test VALUES (1,
'<doc><data>foo</data><stuff>bar</stuff></doc>');
+SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/data|/doc/stuff',
'true');
+
create table articles (article_id integer, article_xml xml, date_entered date);
insert into articles (article_id, article_xml, date_entered)
values (2, '<article><author>test</author><pages>37</pages></article>', now());
diff --git a/contrib/xml2/xml2--1.1.sql b/contrib/xml2/xml2--1.1.sql
index 671372cb27..02588266d0 100644
--- a/contrib/xml2/xml2--1.1.sql
+++ b/contrib/xml2/xml2--1.1.sql
@@ -54,8 +54,14 @@ CREATE FUNCTION xpath_nodeset(text,text,text)
-- Table function
+CREATE FUNCTION xpath_table_describe(internal)
+RETURNS internal
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
CREATE FUNCTION xpath_table(text,text,text,text,text)
RETURNS setof record
+DESCRIBE WITH xpath_table_describe
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT STABLE PARALLEL SAFE;
diff --git a/contrib/xml2/xpath.c b/contrib/xml2/xpath.c
index 1e5b71d9a0..67731509f9 100644
--- a/contrib/xml2/xpath.c
+++ b/contrib/xml2/xpath.c
@@ -7,12 +7,16 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "catalog/namespace.h"
+#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "fmgr.h"
#include "funcapi.h"
#include "lib/stringinfo.h"
#include "miscadmin.h"
#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/regproc.h"
#include "utils/xml.h"
/* libxml includes */
@@ -519,6 +523,74 @@ pgxml_result_to_text(xmlXPathObjectPtr res,
* xpath_table is a table function. It needs some tidying (as do the
* other functions here!
*/
+PG_FUNCTION_INFO_V1(xpath_table_describe);
+
+Datum
+xpath_table_describe(PG_FUNCTION_ARGS)
+{
+ char *pkeyfield;
+ char *xmlfield;
+ char *relname;
+ List *names;
+ Oid relid;
+ AttrNumber pkeyattnum;
+ Oid pkeytype;
+ int32 pkeytypmod;
+ Oid pkeycoll;
+ char *xpathset;
+ const char *pathsep = "|";
+ int numpaths;
+ TupleDesc tupdesc;
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(3))
+ PG_RETURN_NULL();
+
+ pkeyfield = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ xmlfield = text_to_cstring(PG_GETARG_TEXT_PP(1));
+ relname = text_to_cstring(PG_GETARG_TEXT_PP(2));
+ xpathset = text_to_cstring(PG_GETARG_TEXT_PP(3));
+
+ names = stringToQualifiedNameList(relname);
+ relid = RangeVarGetRelid(makeRangeVarFromNameList(names),
AccessShareLock, false);
+
+ pkeyattnum = get_attnum(relid, pkeyfield);
+ if (pkeyattnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does
not exist",
+ pkeyfield, relname)));
+ get_atttypetypmodcoll(relid, pkeyattnum,
+ &pkeytype, &pkeytypmod,
&pkeycoll);
+
+ /* count XPaths */
+ numpaths = 1;
+ for (char *pos = xpathset;;)
+ {
+ pos = strstr(pos, pathsep);
+ if (!pos)
+ break;
+ numpaths++;
+ pos++;
+ }
+
+ tupdesc = CreateTemplateTupleDesc(numpaths + 1);
+
+ TupleDescInitEntry(tupdesc, 1, pkeyfield, pkeytype, pkeytypmod, 0);
+ TupleDescInitEntryCollation(tupdesc, 1, pkeycoll);
+
+ for (int i = 0; i < numpaths; i++)
+ {
+ AttrNumber attnum = 2 + i;
+ char *attname = psprintf("%s%d", xmlfield, i + 1);
+
+ TupleDescInitEntry(tupdesc, attnum, attname, TEXTOID, -1, 0);
+ }
+
+ BlessTupleDesc(tupdesc);
+
+ PG_RETURN_POINTER(tupdesc);
+}
+
PG_FUNCTION_INFO_V1(xpath_table);
Datum
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 55694c4368..0b8f6efe22 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5328,6 +5328,18 @@ <title><structname>pg_proc</structname> Columns</title>
<entry>Data type of the return value</entry>
</row>
+ <row>
+ <entry><structfield>prodescribe</structfield></entry>
+ <entry><type>regproc</type></entry>
+ <entry><literal><link
linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
+ <entry>
+ For functions returning type <type>record</type>, this can point to
+ another function that returns a row description of the result row of
+ this function invocation. See <xref linkend="xfunc-describe"/> for
+ details.
+ </entry>
+ </row>
+
<row>
<entry><structfield>proargtypes</structfield></entry>
<entry><type>oidvector</type></entry>
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 22252556be..5a9d2f9489 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -803,6 +803,14 @@ <title>Table Functions</title>
that the parser knows, for example, what <literal>*</literal> should
expand to.
</para>
+
+ <para>
+ Some functions returning type <type>record</type> allow the column list
+ to be omitted if they can compute the result row type from the constant
+ input arguments. See <xref linkend="xfunc-describe"/> for details. In
+ that case, such functions can be called like functions with a specific
+ composite type return type.
+ </para>
</sect3>
<sect3 id="queries-lateral">
diff --git a/doc/src/sgml/ref/create_function.sgml
b/doc/src/sgml/ref/create_function.sgml
index dd6a2f7304..f53c8bc85f 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -25,6 +25,7 @@
[ RETURNS <replaceable class="parameter">rettype</replaceable>
| RETURNS TABLE ( <replaceable
class="parameter">column_name</replaceable> <replaceable
class="parameter">column_type</replaceable> [, ...] ) ]
{ LANGUAGE <replaceable class="parameter">lang_name</replaceable>
+ | DESCRIBE WITH <replaceable class="parameter">describe_func</replaceable>
| TRANSFORM { FOR TYPE <replaceable
class="parameter">type_name</replaceable> } [, ... ]
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
@@ -262,6 +263,19 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>DESCRIBE WITH <replaceable
class="parameter">describe_func</replaceable></literal></term>
+
+ <listitem>
+ <para>
+ For functions returning type <type>record</type>, this optional clause
+ points to another function that is called to compute the return row
+ structure of a particular call. See <xref linkend="xfunc-describe"/>
+ for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>TRANSFORM { FOR TYPE <replaceable
class="parameter">type_name</replaceable> } [, ... ] }</literal></term>
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index d9afd3be4d..f0b981dbcd 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -3241,6 +3241,72 @@ <title>Polymorphic Arguments and Return Types</title>
</para>
</sect2>
+ <sect2 id="xfunc-describe">
+ <title>Polymorphic Composite Return Type Describe Function</title>
+
+ <para>
+ A function that returns type <type>record</type> normally needs to be
+ called with an explicit column list to specify the names and types of the
+ result columns (see <xref linkend="queries-tablefunctions"/>). In some
+ cases, this can be avoided if the function can inform the database system
+ about the result row structure based on the input parameters of a
+ specific call. To do that, a helper function called the
+ <firstterm>describe</firstterm> function is attached to the
+ record-returning function to communicate the result row structure.
+ </para>
+
+ <para>
+ Consider, as an example, a function that unpacks a CSV document, passed
+ as a single text argument, into a set of rows. It might be defined like
+ this:
+<programlisting>
+CREATE FUNCTION csvtable(doc text) RETURNS SETOF record
+ LANGUAGE C
+ ...
+</programlisting>
+ Because the structure of the CSV document varies, the return type is
+ declared as <type>record</type>. However, if the passed document
+ argument is a constant, then the return row structure can be computed at
+ parse time. To do that, first define a separate describe function:
+<programlisting>
+CREATE FUNCTION cvstable_describe(internal) RETURNS internal
+ LANGUAGE C
+ ...
+</programlisting>
+ and specify that in the definition of the original function:
+<programlisting>
+CREATE FUNCTION csvtable(doc text) RETURNS SETOF record
+ DESCRIBE WITH csvtable_describe
+ LANGUAGE C
+ ...
+</programlisting>
+ (The name of the describe function does not matter.)
+ </para>
+
+ <para>
+ The describe function will be called at parse time with the same argument
+ types as the parent function. Only arguments that are constant at parse
+ time are passed; all other arguments are passed as null values. The
+ describe function must either return a <type>TupleDesc</type> or may
+ return SQL null (not C <symbol>NULL</symbol>) if it cannot compute the
+ row structure with the provided information.
+ </para>
+
+ <para>
+ In the example of the CSV processing function, the describe function
+ could parse the first line of the document to discover the number of
+ fields and their names.
+ </para>
+
+ <para>
+ If the describe function returns a null value, the function call will
+ error. But the same function call could still succeed if the caller
+ provides an explicit column list, as with record-returning functions
+ without a describe helper. If an explicit column list is provided, the
+ describe function is not called at all.
+ </para>
+ </sect2>
+
<sect2>
<title>Shared Memory and LWLocks</title>
diff --git a/src/backend/catalog/pg_aggregate.c
b/src/backend/catalog/pg_aggregate.c
index 1ac235a0f4..04e0436ed3 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -615,6 +615,7 @@ AggregateCreate(const char *aggName,
replace, /*
maybe replacement */
false, /* doesn't
return a set */
finaltype, /*
returnType */
+ InvalidOid, /*
describe */
GetUserId(), /*
proowner */
INTERNALlanguageId,
/* languageObjectId */
InvalidOid, /* no
validator */
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index ef009ad2bc..f2045af3cc 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -70,6 +70,7 @@ ProcedureCreate(const char *procedureName,
bool replace,
bool returnsSet,
Oid returnType,
+ Oid describeFuncId,
Oid proowner,
Oid languageObjectId,
Oid languageValidator,
@@ -331,6 +332,7 @@ ProcedureCreate(const char *procedureName,
values[Anum_pg_proc_pronargs - 1] = UInt16GetDatum(parameterCount);
values[Anum_pg_proc_pronargdefaults - 1] =
UInt16GetDatum(list_length(parameterDefaults));
values[Anum_pg_proc_prorettype - 1] = ObjectIdGetDatum(returnType);
+ values[Anum_pg_proc_prodescribe - 1] = ObjectIdGetDatum(describeFuncId);
values[Anum_pg_proc_proargtypes - 1] = PointerGetDatum(parameterTypes);
if (allParameterTypes != PointerGetDatum(NULL))
values[Anum_pg_proc_proallargtypes - 1] = allParameterTypes;
@@ -629,6 +631,15 @@ ProcedureCreate(const char *procedureName,
referenced.objectSubId = 0;
recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ /* dependency on describe function */
+ if (describeFuncId)
+ {
+ referenced.classId = ProcedureRelationId;
+ referenced.objectId = describeFuncId;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+
/* dependency on transform used by return type, if any */
if ((trfid = get_transform_oid(returnType, languageObjectId, true)))
{
diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index 94411b5008..21497cf38d 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -695,6 +695,7 @@ compute_function_attributes(ParseState *pstate,
List *options,
List **as,
char **language,
+ Node **describe,
Node **transform,
bool *windowfunc_p,
char *volatility_p,
@@ -709,6 +710,7 @@ compute_function_attributes(ParseState *pstate,
{
ListCell *option;
DefElem *as_item = NULL;
+ DefElem *describe_item = NULL;
DefElem *language_item = NULL;
DefElem *transform_item = NULL;
DefElem *windowfunc_item = NULL;
@@ -735,6 +737,15 @@ compute_function_attributes(ParseState *pstate,
parser_errposition(pstate,
defel->location)));
as_item = defel;
}
+ else if (strcmp(defel->defname, "describe") == 0)
+ {
+ if (describe_item)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or
redundant options"),
+ parser_errposition(pstate,
defel->location)));
+ describe_item = defel;
+ }
else if (strcmp(defel->defname, "language") == 0)
{
if (language_item)
@@ -810,6 +821,8 @@ compute_function_attributes(ParseState *pstate,
}
/* process optional items */
+ if (describe_item)
+ *describe = describe_item->arg;
if (transform_item)
*transform = transform_item->arg;
if (windowfunc_item)
@@ -926,6 +939,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
char *language;
Oid languageOid;
Oid languageValidator;
+ Node *describeDefElem = NULL;
Node *transformDefElem = NULL;
char *funcname;
Oid namespaceId;
@@ -936,6 +950,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
ArrayType *parameterNames;
List *parameterDefaults;
Oid variadicArgType;
+ Oid describeFuncOid = InvalidOid;
List *trftypes_list = NIL;
ArrayType *trftypes;
Oid requiredResultType;
@@ -979,7 +994,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
compute_function_attributes(pstate,
stmt->is_procedure,
stmt->options,
- &as_clause,
&language, &transformDefElem,
+ &as_clause,
&language,
+
&describeDefElem, &transformDefElem,
&isWindowFunc,
&volatility,
&isStrict,
&security, &isLeakProof,
&proconfig,
&procost, &prorows,
@@ -1029,6 +1045,17 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("only superuser can define a leakproof
function")));
+ if (describeDefElem)
+ {
+ describeFuncOid = LookupFuncWithArgs(OBJECT_FUNCTION,
castNode(ObjectWithArgs, describeDefElem), false);
+
+ if (get_func_rettype(describeFuncOid) != INTERNALOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("describe function must return
type %s",
+ "internal")));
+ }
+
if (transformDefElem)
{
ListCell *lc;
@@ -1152,6 +1179,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt
*stmt)
stmt->replace,
returnsSet,
prorettype,
+ describeFuncOid,
GetUserId(),
languageOid,
languageValidator,
diff --git a/src/backend/commands/proclang.c b/src/backend/commands/proclang.c
index b51c373b93..c407fda6d5 100644
--- a/src/backend/commands/proclang.c
+++ b/src/backend/commands/proclang.c
@@ -122,6 +122,7 @@ CreateProceduralLanguage(CreatePLangStmt *stmt)
false, /* replace */
false, /* returnsSet */
LANGUAGE_HANDLEROID,
+
InvalidOid, /* describe */
BOOTSTRAP_SUPERUSERID,
ClanguageId,
F_FMGR_C_VALIDATOR,
@@ -162,6 +163,7 @@ CreateProceduralLanguage(CreatePLangStmt *stmt)
false, /* replace */
false, /* returnsSet */
VOIDOID,
+
InvalidOid, /* describe */
BOOTSTRAP_SUPERUSERID,
ClanguageId,
F_FMGR_C_VALIDATOR,
@@ -205,6 +207,7 @@ CreateProceduralLanguage(CreatePLangStmt *stmt)
false, /* replace */
false, /* returnsSet */
VOIDOID,
+
InvalidOid, /* describe */
BOOTSTRAP_SUPERUSERID,
ClanguageId,
F_FMGR_C_VALIDATOR,
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 89887b8fd7..a55d37a301 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1648,6 +1648,7 @@ makeRangeConstructors(const char *name, Oid namespace,
false, /*
replace */
false, /*
returns set */
rangeOid,
/* return type */
+ InvalidOid,
/* describe */
BOOTSTRAP_SUPERUSERID, /* proowner */
INTERNALlanguageId, /* language */
F_FMGR_INTERNAL_VALIDATOR, /* language validator */
diff --git a/src/backend/executor/execSRF.c b/src/backend/executor/execSRF.c
index c8a3efc365..7179b4e88f 100644
--- a/src/backend/executor/execSRF.c
+++ b/src/backend/executor/execSRF.c
@@ -733,6 +733,7 @@ init_sexpr(Oid foid, Oid input_collation, Expr *node,
MemoryContext oldcontext;
functypclass = get_expr_result_type(sexpr->func.fn_expr,
+
false,
&funcrettype,
&tupdesc);
diff --git a/src/backend/executor/nodeFunctionscan.c
b/src/backend/executor/nodeFunctionscan.c
index 0370f2e2b7..0859e1f1fe 100644
--- a/src/backend/executor/nodeFunctionscan.c
+++ b/src/backend/executor/nodeFunctionscan.c
@@ -368,6 +368,7 @@ ExecInitFunctionScan(FunctionScan *node, EState *estate,
int eflags)
* was made; we have to ignore any columns beyond "colcount".
*/
functypclass = get_expr_result_type(funcexpr,
+
rtfunc->funccolnames ? false : true,
&funcrettype,
&tupdesc);
diff --git a/src/backend/optimizer/prep/prepjointree.c
b/src/backend/optimizer/prep/prepjointree.c
index e97c6ec7f6..24d253b0a5 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1719,6 +1719,7 @@ pull_up_constant_function(PlannerInfo *root, Node *jtnode,
return jtnode; /* definitely composite */
functypclass = get_expr_result_type(rtf->funcexpr,
+
false,
&funcrettype,
&tupdesc);
if (functypclass != TYPEFUNC_SCALAR)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..4ad38f317d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -631,7 +631,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
- DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DESC
+ DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DEPENDS DESC
DESCRIBE
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
DOUBLE_P DROP
@@ -7942,6 +7942,10 @@ createfunc_opt_item:
{
$$ = makeDefElem("as", (Node *)$2, @1);
}
+ | DESCRIBE WITH function_with_argtypes
+ {
+ $$ = makeDefElem("describe", (Node
*)$3, @1);
+ }
| LANGUAGE NonReservedWord_or_Sconst
{
$$ = makeDefElem("language", (Node
*)makeString($2), @1);
@@ -15173,6 +15177,7 @@ unreserved_keyword:
| DELIMITER
| DELIMITERS
| DEPENDS
+ | DESCRIBE
| DETACH
| DICTIONARY
| DISABLE_P
diff --git a/src/backend/parser/parse_relation.c
b/src/backend/parser/parse_relation.c
index 47188fcd4f..41a7006254 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1512,6 +1512,7 @@ addRangeTableEntryForFunction(ParseState *pstate,
* Now determine if the function returns a simple or composite
type.
*/
functypclass = get_expr_result_type(funcexpr,
+
coldeflist ? false : true,
&funcrettype,
&tupdesc);
@@ -2307,6 +2308,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int
sublevels_up,
TupleDesc tupdesc;
functypclass =
get_expr_result_type(rtfunc->funcexpr,
+
rtfunc->funccolnames ? false : true,
&funcrettype,
&tupdesc);
if (functypclass == TYPEFUNC_COMPOSITE
||
@@ -2822,6 +2824,7 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber
attnum,
attnum -= atts_done; /* now
relative to this func */
functypclass =
get_expr_result_type(rtfunc->funcexpr,
+
rtfunc->funccolnames ? false : true,
&funcrettype,
&tupdesc);
diff --git a/src/backend/utils/adt/jsonfuncs.c
b/src/backend/utils/adt/jsonfuncs.c
index 1b0fb2afae..139716ce93 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -2256,6 +2256,54 @@ json_to_record(PG_FUNCTION_ARGS)
true, false);
}
+Datum
+json_to_record_describe(PG_FUNCTION_ARGS)
+{
+ text *arg;
+ JsonLexContext *lex;
+ JsonSemAction *sem;
+ OkeysState *state;
+ TupleDesc tupdesc;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ arg = PG_GETARG_TEXT_PP(0);
+
+ lex = makeJsonLexContext(arg, true);
+
+ state = palloc(sizeof(OkeysState));
+ sem = palloc0(sizeof(JsonSemAction));
+
+ state->lex = lex;
+ state->result_size = 256;
+ state->result_count = 0;
+ state->sent_count = 0;
+ state->result = palloc(256 * sizeof(char *));
+
+ sem->semstate = (void *) state;
+ sem->array_start = okeys_array_start;
+ sem->scalar = okeys_scalar;
+ sem->object_field_start = okeys_object_field_start;
+ /* remainder are all NULL, courtesy of palloc0 above */
+
+ pg_parse_json(lex, sem);
+ /* keys are now in state->result */
+
+ pfree(lex->strval->data);
+ pfree(lex->strval);
+ pfree(lex);
+ pfree(sem);
+
+ tupdesc = CreateTemplateTupleDesc(state->result_count);
+ for (int i = 0; i < state->result_count; i++)
+ TupleDescInitEntry(tupdesc, i + 1, state->result[i], TEXTOID,
-1, 0);
+
+ BlessTupleDesc(tupdesc);
+
+ PG_RETURN_POINTER(tupdesc);
+}
+
/* helper function for diagnostics */
static void
populate_array_report_expected_array(PopulateArrayContext *ctx, int ndim)
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
index 4688fbc50c..b592eb5729 100644
--- a/src/backend/utils/fmgr/funcapi.c
+++ b/src/backend/utils/fmgr/funcapi.c
@@ -34,6 +34,7 @@
static void shutdown_MultiFuncCall(Datum arg);
static TypeFuncClass internal_get_result_type(Oid funcid,
Node *call_expr,
+
bool try_describe,
ReturnSetInfo *rsinfo,
Oid *resultTypeId,
TupleDesc *resultTupleDesc);
@@ -199,6 +200,7 @@ get_call_result_type(FunctionCallInfo fcinfo,
{
return internal_get_result_type(fcinfo->flinfo->fn_oid,
fcinfo->flinfo->fn_expr,
+ false,
(ReturnSetInfo *) fcinfo->resultinfo,
resultTypeId,
resultTupleDesc);
@@ -210,6 +212,7 @@ get_call_result_type(FunctionCallInfo fcinfo,
*/
TypeFuncClass
get_expr_result_type(Node *expr,
+ bool try_describe,
Oid *resultTypeId,
TupleDesc *resultTupleDesc)
{
@@ -218,12 +221,14 @@ get_expr_result_type(Node *expr,
if (expr && IsA(expr, FuncExpr))
result = internal_get_result_type(((FuncExpr *) expr)->funcid,
expr,
+
try_describe,
NULL,
resultTypeId,
resultTupleDesc);
else if (expr && IsA(expr, OpExpr))
result = internal_get_result_type(get_opcode(((OpExpr *)
expr)->opno),
expr,
+
try_describe,
NULL,
resultTypeId,
resultTupleDesc);
@@ -292,6 +297,7 @@ get_func_result_type(Oid functionId,
{
return internal_get_result_type(functionId,
NULL,
+ false,
NULL,
resultTypeId,
resultTupleDesc);
@@ -308,6 +314,7 @@ get_func_result_type(Oid functionId,
static TypeFuncClass
internal_get_result_type(Oid funcid,
Node *call_expr,
+ bool try_describe,
ReturnSetInfo *rsinfo,
Oid *resultTypeId,
TupleDesc *resultTupleDesc)
@@ -362,6 +369,46 @@ internal_get_result_type(Oid funcid,
return result;
}
+ if (rettype == RECORDOID && procform->prodescribe && try_describe)
+ {
+ FmgrInfo flinfo;
+ LOCAL_FCINFO(fcinfo, FUNC_MAX_ARGS);
+ Datum funcres;
+ FuncExpr *fexpr;
+
+ fmgr_info(procform->prodescribe, &flinfo);
+ InitFunctionCallInfoData(*fcinfo, &flinfo, procform->pronargs,
InvalidOid, NULL, NULL);
+
+ Assert(call_expr);
+ fexpr = castNode(FuncExpr, call_expr);
+
+ for (int i = 0; i < procform->pronargs; i++)
+ {
+ Node *arg = list_nth(fexpr->args, i);
+
+ if (IsA(arg, Const))
+ {
+ Const *c = castNode(Const, arg);
+
+ fcinfo->args[i].value = c->constvalue;
+ fcinfo->args[i].isnull = c->constisnull;
+ }
+ else
+ fcinfo->args[i].isnull = true;
+ }
+
+ funcres = FunctionCallInvoke(fcinfo);
+
+ if (!fcinfo->isnull)
+ {
+ if (resultTupleDesc)
+ *resultTupleDesc = (TupleDesc)
DatumGetPointer(funcres);
+
+ ReleaseSysCache(tp);
+ return TYPEFUNC_COMPOSITE;
+ }
+ }
+
/*
* If scalar polymorphic result, try to resolve it.
*/
@@ -431,7 +478,7 @@ get_expr_result_tupdesc(Node *expr, bool noError)
TupleDesc tupleDesc;
TypeFuncClass functypclass;
- functypclass = get_expr_result_type(expr, NULL, &tupleDesc);
+ functypclass = get_expr_result_type(expr, true, NULL, &tupleDesc);
if (functypclass == TYPEFUNC_COMPOSITE ||
functypclass == TYPEFUNC_COMPOSITE_DOMAIN)
diff --git a/src/include/catalog/pg_class.dat b/src/include/catalog/pg_class.dat
index 9bcf28676d..48a7529634 100644
--- a/src/include/catalog/pg_class.dat
+++ b/src/include/catalog/pg_class.dat
@@ -44,7 +44,7 @@
relname => 'pg_proc', reltype => 'pg_proc', relam => 'heap',
relfilenode => '0', relpages => '0', reltuples => '0', relallvisible => '0',
reltoastrelid => '0', relhasindex => 'f', relisshared => 'f',
- relpersistence => 'p', relkind => 'r', relnatts => '29', relchecks => '0',
+ relpersistence => 'p', relkind => 'r', relnatts => '30', relchecks => '0',
relhasrules => 'f', relhastriggers => 'f', relhassubclass => 'f',
relrowsecurity => 'f', relforcerowsecurity => 'f', relispopulated => 't',
relreplident => 'n', relispartition => 'f', relfrozenxid => '3',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ac8f64b219..3679535ccd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8332,7 +8332,11 @@
proargtypes => 'anyelement json bool', prosrc => 'json_populate_recordset' },
{ oid => '3204', descr => 'get record fields from a json object',
proname => 'json_to_record', provolatile => 's', prorettype => 'record',
- proargtypes => 'json', prosrc => 'json_to_record' },
+ proargtypes => 'json', prosrc => 'json_to_record',
+ prodescribe => 'json_to_record_describe' },
+{ oid => '3434', descr => 'describe function for json_to_record',
+ proname => 'json_to_record_describe', provolatile => 's', prorettype =>
'internal',
+ proargtypes => 'internal', prosrc => 'json_to_record_describe' },
{ oid => '3205',
descr => 'get set of records with fields from a json array of objects',
proname => 'json_to_recordset', prorows => '100', proisstrict => 'f',
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e6c190926e..abe214d4b7 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -86,6 +86,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP
BKI_ROWTYPE_OID(81,Proce
/* OID of result type */
Oid prorettype BKI_LOOKUP(pg_type);
+ /* function that describes the result row of this function, if 0 if
none */
+ regproc prodescribe BKI_DEFAULT(0) BKI_LOOKUP(pg_proc);
+
/*
* variable-length fields start here, but we allow direct access to
* proargtypes
@@ -182,6 +185,7 @@ extern ObjectAddress ProcedureCreate(const char
*procedureName,
bool
replace,
bool
returnsSet,
Oid
returnType,
+ Oid
describeFuncId,
Oid
proowner,
Oid
languageObjectId,
Oid
languageValidator,
diff --git a/src/include/funcapi.h b/src/include/funcapi.h
index 1aa1f4d8a4..a749d794e1 100644
--- a/src/include/funcapi.h
+++ b/src/include/funcapi.h
@@ -156,6 +156,7 @@ extern TypeFuncClass get_call_result_type(FunctionCallInfo
fcinfo,
Oid *resultTypeId,
TupleDesc *resultTupleDesc);
extern TypeFuncClass get_expr_result_type(Node *expr,
+
bool try_describe,
Oid *resultTypeId,
TupleDesc *resultTupleDesc);
extern TypeFuncClass get_func_result_type(Oid functionId,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 00ace8425e..c44553c8ee 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -129,6 +129,7 @@ PG_KEYWORD("delimiter", DELIMITER, UNRESERVED_KEYWORD)
PG_KEYWORD("delimiters", DELIMITERS, UNRESERVED_KEYWORD)
PG_KEYWORD("depends", DEPENDS, UNRESERVED_KEYWORD)
PG_KEYWORD("desc", DESC, RESERVED_KEYWORD)
+PG_KEYWORD("describe", DESCRIBE, UNRESERVED_KEYWORD)
PG_KEYWORD("detach", DETACH, UNRESERVED_KEYWORD)
PG_KEYWORD("dictionary", DICTIONARY, UNRESERVED_KEYWORD)
PG_KEYWORD("disable", DISABLE_P, UNRESERVED_KEYWORD)
diff --git a/src/interfaces/ecpg/preproc/ecpg.tokens
b/src/interfaces/ecpg/preproc/ecpg.tokens
index 1d613af02f..1bc734018f 100644
--- a/src/interfaces/ecpg/preproc/ecpg.tokens
+++ b/src/interfaces/ecpg/preproc/ecpg.tokens
@@ -5,7 +5,7 @@
SQL_CARDINALITY SQL_CONNECT
SQL_COUNT
SQL_DATETIME_INTERVAL_CODE
- SQL_DATETIME_INTERVAL_PRECISION SQL_DESCRIBE
+ SQL_DATETIME_INTERVAL_PRECISION
SQL_DESCRIPTOR SQL_DISCONNECT SQL_FOUND
SQL_FREE SQL_GET SQL_GO SQL_GOTO SQL_IDENTIFIED
SQL_INDICATOR SQL_KEY_MEMBER SQL_LENGTH
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer
b/src/interfaces/ecpg/preproc/ecpg.trailer
index f58b41e675..528a081d56 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -1058,14 +1058,14 @@ UsingConst: Iconst { $$ = $1; }
/*
* We accept DESCRIBE [OUTPUT] but do nothing with DESCRIBE INPUT so far.
*/
-ECPGDescribe: SQL_DESCRIBE INPUT_P prepared_name using_descriptor
+ECPGDescribe: DESCRIBE INPUT_P prepared_name using_descriptor
{
const char *con = connection ? connection : "NULL";
mmerror(PARSE_ERROR, ET_WARNING, "using unsupported DESCRIBE
statement");
$$ = (char *) mm_alloc(sizeof("1, , ") + strlen(con) +
strlen($3));
sprintf($$, "1, %s, %s", con, $3);
}
- | SQL_DESCRIBE opt_output prepared_name using_descriptor
+ | DESCRIBE opt_output prepared_name using_descriptor
{
const char *con = connection ? connection : "NULL";
struct variable *var;
@@ -1077,20 +1077,20 @@ ECPGDescribe: SQL_DESCRIBE INPUT_P prepared_name
using_descriptor
$$ = (char *) mm_alloc(sizeof("0, , ") + strlen(con) +
strlen($3));
sprintf($$, "0, %s, %s", con, $3);
}
- | SQL_DESCRIBE opt_output prepared_name into_descriptor
+ | DESCRIBE opt_output prepared_name into_descriptor
{
const char *con = connection ? connection : "NULL";
$$ = (char *) mm_alloc(sizeof("0, , ") + strlen(con) +
strlen($3));
sprintf($$, "0, %s, %s", con, $3);
}
- | SQL_DESCRIBE INPUT_P prepared_name into_sqlda
+ | DESCRIBE INPUT_P prepared_name into_sqlda
{
const char *con = connection ? connection : "NULL";
mmerror(PARSE_ERROR, ET_WARNING, "using unsupported DESCRIBE
statement");
$$ = (char *) mm_alloc(sizeof("1, , ") + strlen(con) +
strlen($3));
sprintf($$, "1, %s, %s", con, $3);
}
- | SQL_DESCRIBE opt_output prepared_name into_sqlda
+ | DESCRIBE opt_output prepared_name into_sqlda
{
const char *con = connection ? connection : "NULL";
$$ = (char *) mm_alloc(sizeof("0, , ") + strlen(con) +
strlen($3));
@@ -1502,7 +1502,6 @@ ECPGKeywords_vanames: SQL_BREAK { $$ =
mm_strdup("break"); }
;
ECPGKeywords_rest: SQL_CONNECT { $$ = mm_strdup("connect"); }
- | SQL_DESCRIBE { $$ =
mm_strdup("describe"); }
| SQL_DISCONNECT { $$ =
mm_strdup("disconnect"); }
| SQL_OPEN { $$ =
mm_strdup("open"); }
| SQL_VAR { $$ =
mm_strdup("var"); }
diff --git a/src/interfaces/ecpg/preproc/ecpg_kwlist.h
b/src/interfaces/ecpg/preproc/ecpg_kwlist.h
index bdd9854925..afcf7ec06f 100644
--- a/src/interfaces/ecpg/preproc/ecpg_kwlist.h
+++ b/src/interfaces/ecpg/preproc/ecpg_kwlist.h
@@ -33,7 +33,6 @@ PG_KEYWORD("connect", SQL_CONNECT)
PG_KEYWORD("count", SQL_COUNT)
PG_KEYWORD("datetime_interval_code", SQL_DATETIME_INTERVAL_CODE)
PG_KEYWORD("datetime_interval_precision", SQL_DATETIME_INTERVAL_PRECISION)
-PG_KEYWORD("describe", SQL_DESCRIBE)
PG_KEYWORD("descriptor", SQL_DESCRIPTOR)
PG_KEYWORD("disconnect", SQL_DISCONNECT)
PG_KEYWORD("found", SQL_FOUND)
diff --git a/src/test/regress/expected/json.out
b/src/test/regress/expected/json.out
index c4156cf2a6..66548702b2 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -2219,6 +2219,12 @@ select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
(1 row)
-- json_to_record and json_to_recordset
+select * from json_to_record('{"a":1,"b":"foo","c":"bar"}');
+ a | b | c
+---+-----+-----
+ 1 | foo | bar
+(1 row)
+
select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
a | b | d
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 20354f04e3..7f18d8d900 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -724,6 +724,8 @@ CREATE TEMP TABLE foo (serial_num int, name text, type
text);
-- json_to_record and json_to_recordset
+select * from json_to_record('{"a":1,"b":"foo","c":"bar"}');
+
select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
base-commit: 741b884353e4803abc15d4392ad287b0d5953fc4
--
2.24.1
