Re: UNNEST with multiple args, and TABLE with multiple funcs - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: UNNEST with multiple args, and TABLE with multiple funcs |
| Date | |
| Msg-id | 13944.1384745802@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: UNNEST with multiple args, and TABLE with multiple funcs (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
| Responses |
Re: UNNEST with multiple args, and TABLE with multiple funcs
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Here is a new patch with the following changes on top of Heikki's
> version (all the changes in which I've otherwise kept):
Here is an updated version:
1. Rebased against HEAD.
2. New code is pgindent'ed (mainly because most of the rebasing pain was
because the previous ORDINALITY patch hadn't been pgindented before
committing) and is now git diff --check clean.
3. Fixed build failure due to recent change of
make_pathkey_from_sortinfo's API. I don't think that what I did here is
actually right --- if we're keeping build_expression_pathkey, then
probably it needs to expose nullable_relids as a parameter. But this
will at least make the thing compilable pending review of that point.
This compiles clean and passes regression tests, but I've not done
any actual reviewing yet.
regards, tom lane
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ea930af..8e37ecd 100644
*** a/contrib/pg_stat_statements/pg_stat_statements.c
--- b/contrib/pg_stat_statements/pg_stat_statements.c
*************** JumbleRangeTable(pgssJumbleState *jstate
*** 1456,1462 ****
APP_JUMB(rte->jointype);
break;
case RTE_FUNCTION:
! JumbleExpr(jstate, rte->funcexpr);
break;
case RTE_VALUES:
JumbleExpr(jstate, (Node *) rte->values_lists);
--- 1456,1462 ----
APP_JUMB(rte->jointype);
break;
case RTE_FUNCTION:
! JumbleExpr(jstate, (Node *) rte->funcexprs);
break;
case RTE_VALUES:
JumbleExpr(jstate, (Node *) rte->values_lists);
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a5c808e..d3f773d 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT NULLIF(value, '(none)') ...
*** 11185,11190 ****
--- 11185,11206 ----
<entry><literallayout class="monospaced">1
2</literallayout>(2 rows)</entry>
</row>
+ <row>
+ <entry>
+ <literal>
+ <function>unnest</function>(<type>anyarray</type>, <type>anyarray</type>[,...])
+ </literal>
+ </entry>
+ <entry><type>setof anyelement, anyelement[,...]</type></entry>
+ <entry>expand more than one array to a set of rows. This is
+ only allowed in the FROM clause, and the input arrays can be
+ of different types. For more on table(), which forms the basis of <function>unnest</function>
+ with multiple arrays, see <xref linkend="queries-tablefunctions"></entry>
+ <entry><literal>unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])</literal></entry>
+ <entry><literallayout class="monospaced">1 foo
+ 2 bar
+ NULL baz</literallayout>(3 rows)</entry>
+ </row>
</tbody>
</tgroup>
</table>
*************** AND
*** 13294,13300 ****
The most widely used functions in this class are series generating
functions, as detailed in <xref linkend="functions-srf-series"> and
<xref linkend="functions-srf-subscripts">. Other, more specialized
! set-returning functions are described elsewhere in this manual.
</para>
<table id="functions-srf-series">
--- 13310,13317 ----
The most widely used functions in this class are series generating
functions, as detailed in <xref linkend="functions-srf-series"> and
<xref linkend="functions-srf-subscripts">. Other, more specialized
! set-returning functions are described elsewhere in this manual. See <xref
! linkend="queries-tablefunctions"> for combining multiple set-returning functions.
</para>
<table id="functions-srf-series">
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index c32c857..1ccf753 100644
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
*************** FROM (VALUES ('anne', 'smith'), ('bob',
*** 647,663 ****
</para>
<para>
! If a table function returns a base data type, the single result
! column name matches the function name. If the function returns a
! composite type, the result columns get the same names as the
! individual attributes of the type.
</para>
<para>
! A table function can be aliased in the <literal>FROM</> clause,
! but it also can be left unaliased. If a function is used in the
! <literal>FROM</> clause with no alias, the function name is used
! as the resulting table name.
</para>
<para>
--- 647,706 ----
</para>
<para>
! Table functions may also be combined using the <literal>TABLE</literal>
! syntax, with the results returned in parallel columns; the number of
! result rows in this case is that of the largest function result, with
! smaller results padded with NULLs to match.
</para>
<para>
! If the <literal>WITH ORDINALITY</literal> clause is specified, an
! additional column of type <type>bigint</type> will be appended to the
! function result columns, numbering the rows of the function result set
! starting from 1. (This is a generalization of the SQL-standard syntax
! for <literal>UNNEST ... WITH ORDINALITY</literal>.) The ordinal column
! may be named using a column alias as part of a
! following <literal>AS</literal> clause.
! </para>
!
! <synopsis>
! <replaceable>function_call</replaceable> <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional>
<replaceable>alias</replaceable><optional>(<replaceable>column_alias</replaceable> <optional>,
<replaceable>column_alias</replaceable>... </optional>)</optional></optional>
! TABLE( <replaceable>function_call</replaceable> <optional>, <replaceable>function_call</replaceable> ... </optional>
)<optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>alias</replaceable>
<optional>(<replaceable>column_alias</replaceable><optional>, <replaceable>column_alias</replaceable> ...
</optional>)</optional></optional>
! </synopsis>
!
! <para>
! The special table function <literal>UNNEST</literal> may be called with
! any number of array parameters, and returns a corresponding number of
! columns, as if <literal>UNNEST</literal>
! (see <xref linkend="functions-array">) had been called on each parameter
! separately and combined using the <literal>TABLE</literal> construct. The
! number of result columns is determined by the sum of the number of columns
! in each individual array element type; that is to say, any array of
! composite type is expanded into separate result columns for each field of
! the type. Likewise, the number of rows returned is determined by the
! largest array parameter, with smaller values padded with NULLs.
! </para>
!
! <synopsis>
! UNNEST( <replaceable>array_parameter</replaceable> <optional>, <replaceable>array_parameter</replaceable> ...
</optional>) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>alias</replaceable>
<optional>(<replaceable>column_alias</replaceable><optional>, <replaceable>column_alias</replaceable> ...
</optional>)</optional></optional>
! </synopsis>
!
! <para>
! If a table function returns a base data type, the single result column
! name matches the function name. If the function returns a composite type,
! the result columns get the same names as the individual attributes of the
! type. These rules still apply if multiple functions are used with
! the <literal>TABLE</literal> construct.
! </para>
!
! <para>
! A table function can be aliased in the <literal>FROM</> clause, but it
! also can be left unaliased. If a function is used alone in the
! <literal>FROM</> clause with no alias, the function name is used as the
! resulting table name. If the <literal>TABLE</literal> construct is used
! without an alias, the resulting table name is
! <literal>"table"</literal>. The use of explicit aliases is strongly
! encouraged in this case.
</para>
<para>
*************** SELECT * FROM vw_getfoo;
*** 691,697 ****
the pseudotype <type>record</>. When such a function is used in
a query, the expected row structure must be specified in the
query itself, so that the system can know how to parse and plan
! the query. Consider this example:
<programlisting>
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
--- 734,750 ----
the pseudotype <type>record</>. When such a function is used in
a query, the expected row structure must be specified in the
query itself, so that the system can know how to parse and plan
! the query.
! </para>
!
! <synopsis>
! <replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable>
(<replaceable>column_definition</replaceable><optional>, <replaceable>column_definition</replaceable> ... </optional>)
! <replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional>
(<replaceable>column_definition</replaceable><optional>, <replaceable>column_definition</replaceable> ... </optional>)
! TABLE( <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>,
<replaceable>column_definition</replaceable>... </optional>) <optional>, <replaceable>function_call</replaceable> AS
(<replaceable>column_definition</replaceable><optional>, <replaceable>column_definition</replaceable> ... </optional>)
...</optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional>
<replaceable>alias</replaceable><optional>(<replaceable>column_alias</replaceable> <optional>,
<replaceable>column_alias</replaceable>... </optional>)</optional></optional>
! </synopsis>
!
! <para>
! Consider this example:
<programlisting>
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index e603b76..ba299a1 100644
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** SELECT [ ALL | DISTINCT [ ON ( <replacea
*** 55,60 ****
--- 55,63 ----
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable
class="parameter">argument</replaceable>[, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] <replaceable
class="parameter">alias</replaceable>[ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable
class="parameter">argument</replaceable>[, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> (
<replaceableclass="parameter">column_definition</replaceable> [, ...] )
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable
class="parameter">argument</replaceable>[, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable>
[,...] )
+ [ LATERAL ] TABLE( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable
class="parameter">argument</replaceable>[, ...] ] ) [ AS ( <replaceable
class="parameter">column_definition</replaceable>[, ...] ) ]
+ [, <replaceable class="parameter">function_name</replaceable> ( [ <replaceable
class="parameter">argument</replaceable>[, ...] ] ) [ AS ( <replaceable
class="parameter">column_definition</replaceable>[, ...] ) ] [, ...] ] )
+ [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable
class="parameter">column_alias</replaceable>[, ...] ) ] ]
<replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable
class="parameter">join_type</replaceable><replaceable class="parameter">from_item</replaceable> [ ON <replaceable
class="parameter">join_condition</replaceable>| USING ( <replaceable class="parameter">join_column</replaceable> [,
...]) ]
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index bf47640..fecd10c 100644
*** a/src/backend/access/common/tupdesc.c
--- b/src/backend/access/common/tupdesc.c
*************** CreateTupleDescCopy(TupleDesc tupdesc)
*** 158,197 ****
}
/*
- * CreateTupleDescCopyExtend
- * This function creates a new TupleDesc by copying from an existing
- * TupleDesc, but adding space for more columns. The new tupdesc is
- * not regarded as the same record type as the old one (and therefore
- * does not inherit its typeid/typmod, which instead are left as an
- * anonymous record type).
- *
- * The additional column slots are not initialized in any way;
- * callers must do their own TupleDescInitEntry on each.
- *
- * !!! Constraints and defaults are not copied !!!
- */
- TupleDesc
- CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts)
- {
- TupleDesc desc;
- int i;
- int src_natts = tupdesc->natts;
-
- Assert(moreatts >= 0);
-
- desc = CreateTemplateTupleDesc(src_natts + moreatts, tupdesc->tdhasoid);
-
- for (i = 0; i < src_natts; i++)
- {
- memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE);
- desc->attrs[i]->attnotnull = false;
- desc->attrs[i]->atthasdef = false;
- }
-
- return desc;
- }
-
- /*
* CreateTupleDescCopyConstr
* This function creates a new TupleDesc by copying from an existing
* TupleDesc (including its constraints and defaults).
--- 158,163 ----
*************** TupleDescInitEntryCollation(TupleDesc de
*** 564,569 ****
--- 530,563 ----
desc->attrs[attributeNumber - 1]->attcollation = collationid;
}
+ /*
+ * TupleDescCopyEntry
+ * This function copies a single attribute structure from one tuple
+ * descriptor to another.
+ *
+ * * !!! Constraints and defaults are not copied !!!
+ */
+ void
+ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
+ const TupleDesc src, AttrNumber srcAttno)
+ {
+ /*
+ * sanity checks
+ */
+ AssertArg(PointerIsValid(src));
+ AssertArg(PointerIsValid(dst));
+ AssertArg(srcAttno >= 1);
+ AssertArg(srcAttno <= src->natts);
+ AssertArg(dstAttno >= 1);
+ AssertArg(dstAttno <= dst->natts);
+
+ memcpy(dst->attrs[dstAttno - 1], src->attrs[srcAttno - 1],
+ ATTRIBUTE_FIXED_PART_SIZE);
+ dst->attrs[dstAttno - 1]->attnum = dstAttno;
+ dst->attrs[dstAttno - 1]->attcacheoff = -1;
+ dst->attrs[dstAttno - 1]->attnotnull = false;
+ dst->attrs[dstAttno - 1]->atthasdef = false;
+ }
/*
* BuildDescForRelation
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index fe17c96..2f0ab82 100644
*** a/src/backend/catalog/dependency.c
--- b/src/backend/catalog/dependency.c
*************** find_expr_references_walker(Node *node,
*** 1601,1609 ****
--- 1601,1630 ----
else if (IsA(node, FuncExpr))
{
FuncExpr *funcexpr = (FuncExpr *) node;
+ ListCell *ct;
add_object_address(OCLASS_PROC, funcexpr->funcid, 0,
context->addrs);
+
+ /*
+ * FuncExpr in a function RTE may have a column definition list, in
+ * which case deal with its types and collations
+ */
+ foreach(ct, funcexpr->funccoltypes)
+ {
+ add_object_address(OCLASS_TYPE, lfirst_oid(ct), 0,
+ context->addrs);
+ }
+ foreach(ct, funcexpr->funccolcollations)
+ {
+ Oid collid = lfirst_oid(ct);
+
+ if (OidIsValid(collid) &&
+ collid != DEFAULT_COLLATION_OID)
+ add_object_address(OCLASS_COLLATION, collid, 0,
+ context->addrs);
+ }
+
/* fall through to examine arguments */
}
else if (IsA(node, OpExpr))
*************** find_expr_references_walker(Node *node,
*** 1757,1764 ****
/*
* Add whole-relation refs for each plain relation mentioned in the
! * subquery's rtable, as well as refs for any datatypes and collations
! * used in a RECORD function's output.
*
* Note: query_tree_walker takes care of recursing into RTE_FUNCTION
* RTEs, subqueries, etc, so no need to do that here. But keep it
--- 1778,1786 ----
/*
* Add whole-relation refs for each plain relation mentioned in the
! * subquery's rtable. (Refs for any datatypes and collations used in
! * RECORD function column definitions lists are handled under
! * FuncExpr.)
*
* Note: query_tree_walker takes care of recursing into RTE_FUNCTION
* RTEs, subqueries, etc, so no need to do that here. But keep it
*************** find_expr_references_walker(Node *node,
*** 1771,1777 ****
foreach(lc, query->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
- ListCell *ct;
switch (rte->rtekind)
{
--- 1793,1798 ----
*************** find_expr_references_walker(Node *node,
*** 1779,1800 ****
add_object_address(OCLASS_CLASS, rte->relid, 0,
context->addrs);
break;
- case RTE_FUNCTION:
- foreach(ct, rte->funccoltypes)
- {
- add_object_address(OCLASS_TYPE, lfirst_oid(ct), 0,
- context->addrs);
- }
- foreach(ct, rte->funccolcollations)
- {
- Oid collid = lfirst_oid(ct);
-
- if (OidIsValid(collid) &&
- collid != DEFAULT_COLLATION_OID)
- add_object_address(OCLASS_COLLATION, collid, 0,
- context->addrs);
- }
- break;
default:
break;
}
--- 1800,1805 ----
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 4e93df2..851d43a 100644
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
*************** ExplainNode(PlanState *planstate, List *
*** 1259,1265 ****
break;
case T_FunctionScan:
if (es->verbose)
! show_expression(((FunctionScan *) plan)->funcexpr,
"Function Call", planstate, ancestors,
es->verbose, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
--- 1259,1265 ----
break;
case T_FunctionScan:
if (es->verbose)
! show_expression((Node *) ((FunctionScan *) plan)->funcexprs,
"Function Call", planstate, ancestors,
es->verbose, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
*************** ExplainTargetRel(Plan *plan, Index rti,
*** 1984,2004 ****
break;
case T_FunctionScan:
{
! Node *funcexpr;
/* Assert it's on a RangeFunction */
Assert(rte->rtekind == RTE_FUNCTION);
/*
! * If the expression is still a function call, we can get the
! * real name of the function. Otherwise, punt (this can
! * happen if the optimizer simplified away the function call,
! * for example).
*/
! funcexpr = ((FunctionScan *) plan)->funcexpr;
! if (funcexpr && IsA(funcexpr, FuncExpr))
{
! Oid funcid = ((FuncExpr *) funcexpr)->funcid;
objectname = get_func_name(funcid);
if (es->verbose)
--- 1984,2005 ----
break;
case T_FunctionScan:
{
! FunctionScan *fscan = (FunctionScan *) plan;
/* Assert it's on a RangeFunction */
Assert(rte->rtekind == RTE_FUNCTION);
/*
! * If the expression is still a function call of a single
! * function, we can get the real name of the function.
! * Otherwise, punt. (even if it was a single function call
! * originally, the optimizer could have simplified it away)
*/
! if (fscan->funcexprs && list_length(fscan->funcexprs) == 1 &&
! IsA(linitial(fscan->funcexprs), FuncExpr))
{
! FuncExpr *funcexpr = linitial(fscan->funcexprs);
! Oid funcid = funcexpr->funcid;
objectname = get_func_name(funcid);
if (es->verbose)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index ff6a123..f216cd4 100644
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
*************** sql_fn_post_column_ref(ParseState *pstat
*** 380,387 ****
param = ParseFuncOrColumn(pstate,
list_make1(subfield),
list_make1(param),
! NIL, NULL, false, false, false,
! NULL, true, cref->location);
}
return param;
--- 380,387 ----
param = ParseFuncOrColumn(pstate,
list_make1(subfield),
list_make1(param),
! cref->location,
! NULL);
}
return param;
diff --git a/src/backend/executor/nodeFunctionscan.c b/src/backend/executor/nodeFunctionscan.c
index 423e02f..26f6c30 100644
*** a/src/backend/executor/nodeFunctionscan.c
--- b/src/backend/executor/nodeFunctionscan.c
***************
*** 27,32 ****
--- 27,44 ----
#include "nodes/nodeFuncs.h"
#include "catalog/pg_type.h"
+ /*
+ * Runtime data for each function being scanned.
+ */
+ typedef struct FunctionScanPerFuncState
+ {
+ ExprState *funcexpr; /* state of the FuncExpr being evaluated */
+ TupleDesc tupdesc; /* desc of the function result type */
+ Tuplestorestate *tstore; /* holds the function result set */
+ int64 rowcount; /* number of rows in tstore, -1 if not known */
+ TupleTableSlot *func_slot; /* function result slot (or NULL) */
+ } FunctionScanPerFuncState;
+
static TupleTableSlot *FunctionNext(FunctionScanState *node);
/* ----------------------------------------------------------------
*************** FunctionNext(FunctionScanState *node)
*** 44,119 ****
{
EState *estate;
ScanDirection direction;
- Tuplestorestate *tuplestorestate;
TupleTableSlot *scanslot;
! TupleTableSlot *funcslot;
!
! if (node->func_slot)
! {
! /*
! * ORDINALITY case:
! *
! * We fetch the function result into FUNCSLOT (which matches the
! * function return type), and then copy the values to SCANSLOT
! * (which matches the scan result type), setting the ordinal
! * column in the process.
! */
!
! funcslot = node->func_slot;
! scanslot = node->ss.ss_ScanTupleSlot;
! }
! else
! {
! /*
! * non-ORDINALITY case: the function return type and scan result
! * type are the same, so we fetch the function result straight
! * into the scan result slot.
! */
!
! funcslot = node->ss.ss_ScanTupleSlot;
! scanslot = NULL;
! }
/*
* get information from the estate and scan state
*/
estate = node->ss.ps.state;
direction = estate->es_direction;
! tuplestorestate = node->tuplestorestate;
!
! /*
! * If first time through, read all tuples from function and put them in a
! * tuplestore. Subsequent calls just fetch tuples from tuplestore.
! */
! if (tuplestorestate == NULL)
{
! node->tuplestorestate = tuplestorestate =
! ExecMakeTableFunctionResult(node->funcexpr,
! node->ss.ps.ps_ExprContext,
! node->func_tupdesc,
! node->eflags & EXEC_FLAG_BACKWARD);
! }
!
! /*
! * Get the next tuple from tuplestore. Return NULL if no more tuples.
! */
! (void) tuplestore_gettupleslot(tuplestorestate,
! ScanDirectionIsForward(direction),
! false,
! funcslot);
! if (!scanslot)
! return funcslot;
! /*
! * we're doing ordinality, so we copy the values from the function return
! * slot to the (distinct) scan slot. We can do this because the lifetimes
! * of the values in each slot are the same; until we reset the scan or
! * fetch the next tuple, both will be valid.
! */
! ExecClearTuple(scanslot);
/*
* increment or decrement before checking for end-of-data, so that we can
--- 56,118 ----
{
EState *estate;
ScanDirection direction;
TupleTableSlot *scanslot;
! bool alldone;
! int64 oldpos;
! int funcno;
! int att;
/*
* get information from the estate and scan state
*/
estate = node->ss.ps.state;
direction = estate->es_direction;
+ scanslot = node->ss.ss_ScanTupleSlot;
! if (node->simple)
{
! /*
! * Fast path for the trivial case: the function return type and scan
! * result type are the same, so we fetch the function result straight
! * into the scan result slot. No need to update ordinality or
! * rowcounts either.
! */
! Tuplestorestate *tstore = node->funcstates[0].tstore;
! /*
! * If first time through, read all tuples from function and put them
! * in a tuplestore. Subsequent calls just fetch tuples from
! * tuplestore.
! */
! if (tstore == NULL)
! {
! node->funcstates[0].tstore = tstore =
! ExecMakeTableFunctionResult(node->funcstates[0].funcexpr,
! node->ss.ps.ps_ExprContext,
! node->funcstates[0].tupdesc,
! node->eflags & EXEC_FLAG_BACKWARD);
! /*
! * paranoia - cope if the function, which may have constructed the
! * tuplestore itself, didn't leave it pointing at the start. This
! * call is fast, so the overhead shouldn't be an issue.
! */
! tuplestore_rescan(tstore);
! }
! /*
! * Get the next tuple from tuplestore.
! *
! * If we have a rowcount for the function, and we know the previous
! * read position was out of bounds, don't try the read. This allows
! * backward scan to work when there are mixed row counts present.
! */
! (void) tuplestore_gettupleslot(tstore,
! ScanDirectionIsForward(direction),
! false,
! scanslot);
! return scanslot;
! }
/*
* increment or decrement before checking for end-of-data, so that we can
*************** FunctionNext(FunctionScanState *node)
*** 121,151 ****
* losing correct count. See PortalRunSelect for why we assume that we
* won't be called repeatedly in the end-of-data state.
*/
!
if (ScanDirectionIsForward(direction))
! node->ordinal++;
else
! node->ordinal--;
! if (!TupIsNull(funcslot))
{
! int natts = funcslot->tts_tupleDescriptor->natts;
! int i;
! slot_getallattrs(funcslot);
! for (i = 0; i < natts; ++i)
{
! scanslot->tts_values[i] = funcslot->tts_values[i];
! scanslot->tts_isnull[i] = funcslot->tts_isnull[i];
}
! scanslot->tts_values[natts] = Int64GetDatumFast(node->ordinal);
! scanslot->tts_isnull[natts] = false;
! ExecStoreVirtualTuple(scanslot);
}
return scanslot;
}
--- 120,239 ----
* losing correct count. See PortalRunSelect for why we assume that we
* won't be called repeatedly in the end-of-data state.
*/
! oldpos = node->ordinal;
if (ScanDirectionIsForward(direction))
! oldpos = node->ordinal++;
else
! oldpos = node->ordinal--;
! /*
! * Main loop over functions.
! *
! * We fetch the function results into FUNCSLOTs (which match the function
! * return types), and then copy the values to SCANSLOT (which matches the
! * scan result type), setting the ordinal column (if any) in the process.
! */
! att = 0;
! alldone = true;
! ExecClearTuple(scanslot);
! for (funcno = 0; funcno < node->nfuncs; funcno++)
{
! FunctionScanPerFuncState *fs = &node->funcstates[funcno];
! int i;
! /*
! * If first time through, read all tuples from function and put them
! * in a tuplestore. Subsequent calls just fetch tuples from
! * tuplestore.
! */
! if (fs->tstore == NULL)
! {
! fs->tstore =
! ExecMakeTableFunctionResult(fs->funcexpr,
! node->ss.ps.ps_ExprContext,
! fs->tupdesc,
! node->eflags & EXEC_FLAG_BACKWARD);
! /*
! * paranoia - cope if the function, which may have constructed the
! * tuplestore itself, didn't leave it pointing at the start. This
! * call is fast, so the overhead shouldn't be an issue.
! */
! tuplestore_rescan(fs->tstore);
! }
!
! /*
! * Get the next tuple from tuplestore.
! *
! * If we have a rowcount for the function, and we know the previous
! * read position was out of bounds, don't try the read. This allows
! * backward scan to work when there are mixed row counts present.
! */
! if (fs->rowcount != -1 && fs->rowcount < oldpos)
! ExecClearTuple(fs->func_slot);
! else
! (void) tuplestore_gettupleslot(fs->tstore,
! ScanDirectionIsForward(direction),
! false,
! fs->func_slot);
!
! if (TupIsNull(fs->func_slot))
{
! /*
! * If we ran out of data for this function in the forward
! * direction then we now know how many rows it returned. We need
! * to know this in order to handle backwards scans. The row count
! * we store is actually 1+ the actual number, because we have to
! * position the tuplestore 1 off its end sometimes.
! */
! if (ScanDirectionIsForward(direction) && fs->rowcount == -1)
! fs->rowcount = node->ordinal;
!
! /*
! * populate our result cols with null
! */
! for (i = 0; i < fs->tupdesc->natts; i++)
! {
! scanslot->tts_values[att] = (Datum) 0;
! scanslot->tts_isnull[att] = true;
! att++;
! }
}
+ else
+ {
+ /*
+ * we have a result, so just copy it to the result cols.
+ */
! slot_getallattrs(fs->func_slot);
! for (i = 0; i < fs->tupdesc->natts; i++)
! {
! scanslot->tts_values[att] = fs->func_slot->tts_values[i];
! scanslot->tts_isnull[att] = fs->func_slot->tts_isnull[i];
! att++;
! }
!
! /*
! * We're not done until every function result is exhausted; we pad
! * the shorter results with nulls until then.
! */
! alldone = false;
! }
! }
!
! /*
! * ordinal col is always last, per spec.
! */
! if (node->ordinality)
! {
! scanslot->tts_values[att] = Int64GetDatumFast(node->ordinal);
! scanslot->tts_isnull[att] = false;
}
+ if (!alldone)
+ ExecStoreVirtualTuple(scanslot);
+
return scanslot;
}
*************** ExecInitFunctionScan(FunctionScan *node,
*** 186,193 ****
FunctionScanState *scanstate;
Oid funcrettype;
TypeFuncClass functypclass;
- TupleDesc func_tupdesc = NULL;
TupleDesc scan_tupdesc = NULL;
/* check for unsupported flags */
Assert(!(eflags & EXEC_FLAG_MARK));
--- 274,284 ----
FunctionScanState *scanstate;
Oid funcrettype;
TypeFuncClass functypclass;
TupleDesc scan_tupdesc = NULL;
+ int nfuncs = list_length(node->funcexprs);
+ int i,
+ natts;
+ ListCell *lc;
/* check for unsupported flags */
Assert(!(eflags & EXEC_FLAG_MARK));
*************** ExecInitFunctionScan(FunctionScan *node,
*** 207,212 ****
--- 298,326 ----
scanstate->eflags = eflags;
/*
+ * are we adding an ordinality column?
+ */
+ scanstate->ordinality = node->funcordinality;
+
+ scanstate->nfuncs = nfuncs;
+ if (nfuncs == 1 && !node->funcordinality)
+ scanstate->simple = true;
+ else
+ scanstate->simple = false;
+
+ /*
+ * Ordinal 0 represents the "before the first row" position.
+ *
+ * We need to track ordinal position even when not adding an ordinality
+ * column to the result, in order to handle backwards scanning properly
+ * with multiple functions with different result sizes. (We can't position
+ * any individual function's tuplestore any more than 1 place beyond its
+ * end, so when scanning backwards, we need to know when to start
+ * including the function in the scan again.)
+ */
+ scanstate->ordinal = 0;
+
+ /*
* Miscellaneous initialization
*
* create expression context for node
*************** ExecInitFunctionScan(FunctionScan *node,
*** 220,235 ****
ExecInitScanTupleSlot(estate, &scanstate->ss);
/*
- * We only need a separate slot for the function result if we are doing
- * ordinality; otherwise, we fetch function results directly into the
- * scan slot.
- */
- if (node->funcordinality)
- scanstate->func_slot = ExecInitExtraTupleSlot(estate);
- else
- scanstate->func_slot = NULL;
-
- /*
* initialize child expressions
*/
scanstate->ss.ps.targetlist = (List *)
--- 334,339 ----
*************** ExecInitFunctionScan(FunctionScan *node,
*** 239,351 ****
ExecInitExpr((Expr *) node->scan.plan.qual,
(PlanState *) scanstate);
! /*
! * Now determine if the function returns a simple or composite
! * type, and build an appropriate tupdesc. This tupdesc
! * (func_tupdesc) is the one that matches the shape of the
! * function result, no extra columns.
! */
! functypclass = get_expr_result_type(node->funcexpr,
! &funcrettype,
! &func_tupdesc);
! if (functypclass == TYPEFUNC_COMPOSITE)
{
! /* Composite data type, e.g. a table's row type */
! Assert(func_tupdesc);
/*
! * XXX
! * Existing behaviour is a bit inconsistent with regard to aliases and
! * whole-row Vars of the function result. If the function returns a
! * composite type, then the whole-row Var will refer to this tupdesc,
! * which has the type's own column names rather than the alias column
! * names given in the query. This affects the output of constructs like
! * row_to_json which read the column names from the passed-in values.
*/
! /* Must copy it out of typcache for safety */
! func_tupdesc = CreateTupleDescCopy(func_tupdesc);
! }
! else if (functypclass == TYPEFUNC_SCALAR)
! {
! /* Base data type, i.e. scalar */
! char *attname = strVal(linitial(node->funccolnames));
! func_tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(func_tupdesc,
! (AttrNumber) 1,
! attname,
! funcrettype,
! -1,
! 0);
! TupleDescInitEntryCollation(func_tupdesc,
! (AttrNumber) 1,
! exprCollation(node->funcexpr));
! }
! else if (functypclass == TYPEFUNC_RECORD)
! {
! func_tupdesc = BuildDescFromLists(node->funccolnames,
! node->funccoltypes,
! node->funccoltypmods,
! node->funccolcollations);
! }
! else
! {
! /* crummy error message, but parser should have caught this */
! elog(ERROR, "function in FROM has unsupported return type");
! }
! /*
! * For RECORD results, make sure a typmod has been assigned. (The
! * function should do this for itself, but let's cover things in case it
! * doesn't.)
! */
! BlessTupleDesc(func_tupdesc);
/*
! * If doing ordinality, we need a new tupdesc with one additional column
! * tacked on, always of type "bigint". The name to use has already been
! * recorded by the parser as the last element of funccolnames.
*
! * Without ordinality, the scan result tupdesc is the same as the
! * function result tupdesc. (No need to make a copy.)
*/
! if (node->funcordinality)
{
! int natts = func_tupdesc->natts;
! scan_tupdesc = CreateTupleDescCopyExtend(func_tupdesc, 1);
! TupleDescInitEntry(scan_tupdesc,
! natts + 1,
! strVal(llast(node->funccolnames)),
! INT8OID,
! -1,
! 0);
! BlessTupleDesc(scan_tupdesc);
! }
! else
! scan_tupdesc = func_tupdesc;
! scanstate->scan_tupdesc = scan_tupdesc;
! scanstate->func_tupdesc = func_tupdesc;
! ExecAssignScanType(&scanstate->ss, scan_tupdesc);
! if (scanstate->func_slot)
! ExecSetSlotDescriptor(scanstate->func_slot, func_tupdesc);
/*
! * Other node-specific setup
*/
! scanstate->ordinal = 0;
! scanstate->tuplestorestate = NULL;
! scanstate->funcexpr = ExecInitExpr((Expr *) node->funcexpr,
! (PlanState *) scanstate);
! scanstate->ss.ps.ps_TupFromTlist = false;
/*
* Initialize result tuple type and projection info.
--- 343,495 ----
ExecInitExpr((Expr *) node->scan.plan.qual,
(PlanState *) scanstate);
! scanstate->funcstates = palloc(nfuncs * sizeof(FunctionScanPerFuncState));
! i = 0;
! natts = 0;
!
! foreach(lc, node->funcexprs)
{
! Expr *funcexpr = (Expr *) lfirst(lc);
! FunctionScanPerFuncState *fs = &scanstate->funcstates[i];
! TupleDesc tupdesc;
!
! fs->funcexpr = ExecInitExpr(funcexpr, (PlanState *) scanstate);
/*
! * don't allocate the tuplestores; the actual call to the function
! * does that. NULL flags that we have not called the function yet (or
! * need to call it again after a rescan).
*/
+ fs->tstore = NULL;
+ fs->rowcount = -1;
! /*
! * Determine if this function returns a simple or composite type, and
! * build an appropriate tupdesc. This tupdesc is the one that matches
! * the shape of the function result, no extra columns.
! */
! functypclass = get_expr_result_type((Node *) funcexpr,
! &funcrettype,
! &tupdesc);
! if (functypclass == TYPEFUNC_COMPOSITE)
! {
! /* Composite data type, e.g. a table's row type */
! Assert(tupdesc);
! /*
! * XXX Existing behaviour is a bit inconsistent with regard to
! * aliases and whole-row Vars of the function result. If the
! * function returns a composite type, then the whole-row Var will
! * refer to this tupdesc, which has the type's own column names
! * rather than the alias column names given in the query. This
! * affects the output of constructs like row_to_json which read
! * the column names from the passed-in values.
! */
!
! /* Must copy it out of typcache for safety (?) */
! tupdesc = CreateTupleDescCopy(tupdesc);
!
! natts += tupdesc->natts;
! }
! else if (functypclass == TYPEFUNC_SCALAR)
! {
! /* Base data type, i.e. scalar */
! char *attname = strVal(list_nth(node->funccolnames, natts));
!
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc,
! (AttrNumber) 1,
! attname,
! funcrettype,
! -1,
! 0);
! TupleDescInitEntryCollation(tupdesc,
! (AttrNumber) 1,
! exprCollation((Node *) funcexpr));
! natts++;
! }
! else
! {
! /* crummy error message, but parser should have caught this */
! elog(ERROR, "function in FROM has unsupported return type");
! }
! fs->tupdesc = tupdesc;
!
! /*
! * We only need separate slots for the function results if we are
! * doing ordinality or multiple functions; otherwise, we fetch
! * function results directly into the scan slot.
! */
! if (!scanstate->simple)
! {
! fs->func_slot = ExecInitExtraTupleSlot(estate);
! ExecSetSlotDescriptor(fs->func_slot, fs->tupdesc);
! }
! else
! fs->func_slot = NULL;
!
! i++;
! }
/*
! * Create the combined TupleDesc
*
! * If there is just one function without ordinality, the scan result
! * tupdesc is the same as the function result tupdesc. (No need to make a
! * copy.)
*/
! if (scanstate->simple)
{
! scan_tupdesc = scanstate->funcstates[0].tupdesc;
! }
! else
! {
! AttrNumber attno = 0;
! if (node->funcordinality)
! natts++;
! scan_tupdesc = CreateTemplateTupleDesc(natts, false);
! for (i = 0; i < nfuncs; i++)
! {
! TupleDesc tupdesc = scanstate->funcstates[i].tupdesc;
! int j;
! for (j = 1; j <= tupdesc->natts; j++)
! TupleDescCopyEntry(scan_tupdesc, ++attno, tupdesc, j);
! }
! if (node->funcordinality)
! {
! /*
! * If doing ordinality, add a column of type "bigint" at the end.
! * The column name to use has already been recorded by the parser
! * as the last element of funccolnames.
! */
! TupleDescInitEntry(scan_tupdesc,
! ++attno,
! strVal(llast(node->funccolnames)),
! INT8OID,
! -1,
! 0);
! }
!
! Assert(attno == natts);
! }
/*
! * We didn't necessarily bless all the individual function tupdescs, but
! * we have to ensure that the scan result tupdesc is, regardless of where
! * it came from.
*/
! BlessTupleDesc(scan_tupdesc);
! scanstate->scan_tupdesc = scan_tupdesc;
! ExecAssignScanType(&scanstate->ss, scan_tupdesc);
/*
* Initialize result tuple type and projection info.
*************** ExecInitFunctionScan(FunctionScan *node,
*** 353,358 ****
--- 497,504 ----
ExecAssignResultTypeFromTL(&scanstate->ss.ps);
ExecAssignScanProjectionInfo(&scanstate->ss);
+ scanstate->ss.ps.ps_TupFromTlist = false;
+
return scanstate;
}
*************** ExecInitFunctionScan(FunctionScan *node,
*** 365,370 ****
--- 511,518 ----
void
ExecEndFunctionScan(FunctionScanState *node)
{
+ int i;
+
/*
* Free the exprcontext
*/
*************** ExecEndFunctionScan(FunctionScanState *n
*** 375,389 ****
*/
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
ExecClearTuple(node->ss.ss_ScanTupleSlot);
- if (node->func_slot)
- ExecClearTuple(node->func_slot);
/*
! * Release tuplestore resources
*/
! if (node->tuplestorestate != NULL)
! tuplestore_end(node->tuplestorestate);
! node->tuplestorestate = NULL;
}
/* ----------------------------------------------------------------
--- 523,545 ----
*/
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
ExecClearTuple(node->ss.ss_ScanTupleSlot);
/*
! * Release slots and tuplestore resources
*/
! for (i = 0; i < node->nfuncs; i++)
! {
! FunctionScanPerFuncState *fs = &node->funcstates[i];
!
! if (fs->func_slot)
! ExecClearTuple(fs->func_slot);
!
! if (fs->tstore != NULL)
! {
! tuplestore_end(node->funcstates[i].tstore);
! fs->tstore = NULL;
! }
! }
}
/* ----------------------------------------------------------------
*************** ExecEndFunctionScan(FunctionScanState *n
*** 395,425 ****
void
ExecReScanFunctionScan(FunctionScanState *node)
{
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
! if (node->func_slot)
! ExecClearTuple(node->func_slot);
ExecScanReScan(&node->ss);
node->ordinal = 0;
/*
! * If we haven't materialized yet, just return.
*/
! if (!node->tuplestorestate)
! return;
! /*
! * Here we have a choice whether to drop the tuplestore (and recompute the
! * function outputs) or just rescan it. We must recompute if the
! * expression contains parameters, else we rescan. XXX maybe we should
! * recompute if the function is volatile?
! */
! if (node->ss.ps.chgParam != NULL)
{
! tuplestore_end(node->tuplestorestate);
! node->tuplestorestate = NULL;
}
- else
- tuplestore_rescan(node->tuplestorestate);
}
--- 551,607 ----
void
ExecReScanFunctionScan(FunctionScanState *node)
{
+ FunctionScan *scan = (FunctionScan *) node->ss.ps.plan;
+ int i;
+ Bitmapset *chgparam = node->ss.ps.chgParam;
+
ExecClearTuple(node->ss.ps.ps_ResultTupleSlot);
! for (i = 0; i < node->nfuncs; ++i)
! {
! FunctionScanPerFuncState *fs = &node->funcstates[i];
!
! if (fs->func_slot)
! ExecClearTuple(fs->func_slot);
! }
ExecScanReScan(&node->ss);
node->ordinal = 0;
/*
! * Here we have a choice whether to drop the tuplestores (and recompute
! * the function outputs) or just rescan them. We must recompute if the
! * expression contains changed parameters, else we rescan.
! *
! * Note that if chgparam is NULL, it's possible that the funcparams list
! * may be empty (if there never were any params and so finalize_plan was
! * never called), so we have to be careful about iterating or referencing
! * it.
! *
! * XXX maybe we should recompute if the function is volatile?
*/
! if (chgparam)
! {
! ListCell *lc;
! i = 0;
! foreach(lc, scan->funcparams)
! {
! if (bms_overlap(chgparam, lfirst(lc)))
! {
! if (node->funcstates[i].tstore != NULL)
! {
! tuplestore_end(node->funcstates[i].tstore);
! node->funcstates[i].tstore = NULL;
! }
! node->funcstates[i].rowcount = -1;
! }
! i++;
! }
! }
! for (i = 0; i < node->nfuncs; ++i)
{
! if (node->funcstates[i].tstore != NULL)
! tuplestore_rescan(node->funcstates[i].tstore);
}
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1733da6..2859d51 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** static FunctionScan *
*** 495,500 ****
--- 495,501 ----
_copyFunctionScan(const FunctionScan *from)
{
FunctionScan *newnode = makeNode(FunctionScan);
+ ListCell *lc;
/*
* copy node superclass fields
*************** _copyFunctionScan(const FunctionScan *fr
*** 504,516 ****
/*
* copy remainder of node
*/
! COPY_NODE_FIELD(funcexpr);
COPY_NODE_FIELD(funccolnames);
- COPY_NODE_FIELD(funccoltypes);
- COPY_NODE_FIELD(funccoltypmods);
- COPY_NODE_FIELD(funccolcollations);
COPY_SCALAR_FIELD(funcordinality);
return newnode;
}
--- 505,522 ----
/*
* copy remainder of node
*/
! COPY_NODE_FIELD(funcexprs);
COPY_NODE_FIELD(funccolnames);
COPY_SCALAR_FIELD(funcordinality);
+ /*
+ * copy the param bitmap list by shallow-copying the list structure, then
+ * replacing the values with copies
+ */
+ newnode->funcparams = list_copy(from->funcparams);
+ foreach(lc, newnode->funcparams)
+ lfirst(lc) = bms_copy(lfirst(lc));
+
return newnode;
}
*************** _copyFuncExpr(const FuncExpr *from)
*** 1206,1211 ****
--- 1212,1221 ----
COPY_SCALAR_FIELD(funccollid);
COPY_SCALAR_FIELD(inputcollid);
COPY_NODE_FIELD(args);
+ COPY_NODE_FIELD(funccolnames);
+ COPY_NODE_FIELD(funccoltypes);
+ COPY_NODE_FIELD(funccoltypmods);
+ COPY_NODE_FIELD(funccolcollations);
COPY_LOCATION_FIELD(location);
return newnode;
*************** _copyRangeTblEntry(const RangeTblEntry *
*** 1981,1990 ****
COPY_SCALAR_FIELD(security_barrier);
COPY_SCALAR_FIELD(jointype);
COPY_NODE_FIELD(joinaliasvars);
! COPY_NODE_FIELD(funcexpr);
! COPY_NODE_FIELD(funccoltypes);
! COPY_NODE_FIELD(funccoltypmods);
! COPY_NODE_FIELD(funccolcollations);
COPY_SCALAR_FIELD(funcordinality);
COPY_NODE_FIELD(values_lists);
COPY_NODE_FIELD(values_collations);
--- 1991,1997 ----
COPY_SCALAR_FIELD(security_barrier);
COPY_SCALAR_FIELD(jointype);
COPY_NODE_FIELD(joinaliasvars);
! COPY_NODE_FIELD(funcexprs);
COPY_SCALAR_FIELD(funcordinality);
COPY_NODE_FIELD(values_lists);
COPY_NODE_FIELD(values_collations);
*************** _copyFuncCall(const FuncCall *from)
*** 2175,2180 ****
--- 2182,2188 ----
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
COPY_NODE_FIELD(over);
+ COPY_NODE_FIELD(coldeflist);
COPY_LOCATION_FIELD(location);
return newnode;
*************** _copyRangeFunction(const RangeFunction *
*** 2301,2309 ****
COPY_SCALAR_FIELD(ordinality);
COPY_SCALAR_FIELD(lateral);
! COPY_NODE_FIELD(funccallnode);
COPY_NODE_FIELD(alias);
- COPY_NODE_FIELD(coldeflist);
return newnode;
}
--- 2309,2317 ----
COPY_SCALAR_FIELD(ordinality);
COPY_SCALAR_FIELD(lateral);
! COPY_SCALAR_FIELD(is_table);
! COPY_NODE_FIELD(funccallnodes);
COPY_NODE_FIELD(alias);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 7b29812..ef77f97 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalFuncExpr(const FuncExpr *a, const
*** 248,253 ****
--- 248,257 ----
COMPARE_SCALAR_FIELD(funccollid);
COMPARE_SCALAR_FIELD(inputcollid);
COMPARE_NODE_FIELD(args);
+ COMPARE_NODE_FIELD(funccolnames);
+ COMPARE_NODE_FIELD(funccoltypes);
+ COMPARE_NODE_FIELD(funccoltypmods);
+ COMPARE_NODE_FIELD(funccolcollations);
COMPARE_LOCATION_FIELD(location);
return true;
*************** _equalFuncCall(const FuncCall *a, const
*** 2016,2021 ****
--- 2020,2026 ----
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
COMPARE_NODE_FIELD(over);
+ COMPARE_NODE_FIELD(coldeflist);
COMPARE_LOCATION_FIELD(location);
return true;
*************** _equalRangeFunction(const RangeFunction
*** 2142,2150 ****
{
COMPARE_SCALAR_FIELD(ordinality);
COMPARE_SCALAR_FIELD(lateral);
! COMPARE_NODE_FIELD(funccallnode);
COMPARE_NODE_FIELD(alias);
- COMPARE_NODE_FIELD(coldeflist);
return true;
}
--- 2147,2155 ----
{
COMPARE_SCALAR_FIELD(ordinality);
COMPARE_SCALAR_FIELD(lateral);
! COMPARE_SCALAR_FIELD(is_table);
! COMPARE_NODE_FIELD(funccallnodes);
COMPARE_NODE_FIELD(alias);
return true;
}
*************** _equalRangeTblEntry(const RangeTblEntry
*** 2245,2254 ****
COMPARE_SCALAR_FIELD(security_barrier);
COMPARE_SCALAR_FIELD(jointype);
COMPARE_NODE_FIELD(joinaliasvars);
! COMPARE_NODE_FIELD(funcexpr);
! COMPARE_NODE_FIELD(funccoltypes);
! COMPARE_NODE_FIELD(funccoltypmods);
! COMPARE_NODE_FIELD(funccolcollations);
COMPARE_SCALAR_FIELD(funcordinality);
COMPARE_NODE_FIELD(values_lists);
COMPARE_NODE_FIELD(values_collations);
--- 2250,2256 ----
COMPARE_SCALAR_FIELD(security_barrier);
COMPARE_SCALAR_FIELD(jointype);
COMPARE_NODE_FIELD(joinaliasvars);
! COMPARE_NODE_FIELD(funcexprs);
COMPARE_SCALAR_FIELD(funcordinality);
COMPARE_NODE_FIELD(values_lists);
COMPARE_NODE_FIELD(values_collations);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 4a7e793..90beca2 100644
*** a/src/backend/nodes/makefuncs.c
--- b/src/backend/nodes/makefuncs.c
*************** makeVarFromTargetEntry(Index varno,
*** 127,135 ****
* the function's result directly, instead of the single-column composite
* value that the whole-row notation might otherwise suggest.
*
! * We also handle the specific case of function RTEs with ordinality,
! * where the additional column has to be added. This forces the result
! * to be composite and RECORD type.
*/
Var *
makeWholeRowVar(RangeTblEntry *rte,
--- 127,135 ----
* the function's result directly, instead of the single-column composite
* value that the whole-row notation might otherwise suggest.
*
! * We also handle the specific case of function RTEs with ordinality or
! * multiple function calls. This forces the result to be composite and RECORD
! * type.
*/
Var *
makeWholeRowVar(RangeTblEntry *rte,
*************** makeWholeRowVar(RangeTblEntry *rte,
*** 157,179 ****
break;
case RTE_FUNCTION:
/*
! * RTE is a function with or without ordinality. We map the
! * cases as follows:
*
! * If ordinality is set, we return a composite var even if
! * the function is a scalar. This var is always of RECORD type.
*
! * If ordinality is not set but the function returns a row,
! * we keep the function's return type.
*
* If the function is a scalar, we do what allowScalar requests.
*/
! toid = exprType(rte->funcexpr);
! if (rte->funcordinality)
{
! /* ORDINALITY always produces an anonymous RECORD result */
result = makeVar(varno,
InvalidAttrNumber,
RECORDOID,
--- 157,183 ----
break;
case RTE_FUNCTION:
+
/*
! * RTE is a function with or without ordinality. We map the cases
! * as follows:
*
! * If ordinality is set, we return a composite var even if the
! * function is a scalar. This var is always of RECORD type.
*
! * If the RTE has more than one function, we return a composite
! * var of record type.
! *
! * If ordinality is not set but the function returns a row, we
! * keep the function's return type.
*
* If the function is a scalar, we do what allowScalar requests.
*/
! toid = exprType(linitial(rte->funcexprs));
! if (rte->funcordinality || list_length(rte->funcexprs) > 1)
{
! /* always produces an anonymous RECORD result */
result = makeVar(varno,
InvalidAttrNumber,
RECORDOID,
*************** makeWholeRowVar(RangeTblEntry *rte,
*** 198,204 ****
1,
toid,
-1,
! exprCollation(rte->funcexpr),
varlevelsup);
}
else
--- 202,208 ----
1,
toid,
-1,
! exprCollation(linitial(rte->funcexprs)),
varlevelsup);
}
else
*************** makeFuncExpr(Oid funcid, Oid rettype, Li
*** 494,499 ****
--- 498,507 ----
funcexpr->funccollid = funccollid;
funcexpr->inputcollid = inputcollid;
funcexpr->args = args;
+ funcexpr->funccolnames = NIL;
+ funcexpr->funccoltypes = NIL;
+ funcexpr->funccoltypmods = NIL;
+ funcexpr->funccolcollations = NIL;
funcexpr->location = -1;
return funcexpr;
*************** makeDefElemExtended(char *nameSpace, cha
*** 549,555 ****
FuncCall *
makeFuncCall(List *name, List *args, int location)
{
! FuncCall *n = makeNode(FuncCall);
n->funcname = name;
n->args = args;
n->location = location;
--- 557,564 ----
FuncCall *
makeFuncCall(List *name, List *args, int location)
{
! FuncCall *n = makeNode(FuncCall);
!
n->funcname = name;
n->args = args;
n->location = location;
*************** makeFuncCall(List *name, List *args, int
*** 559,563 ****
--- 568,573 ----
n->agg_distinct = FALSE;
n->func_variadic = FALSE;
n->over = NULL;
+ n->coldeflist = NULL;
return n;
}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 908f397..e0113ef 100644
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
*************** range_table_walker(List *rtable,
*** 2000,2006 ****
return true;
break;
case RTE_FUNCTION:
! if (walker(rte->funcexpr, context))
return true;
break;
case RTE_VALUES:
--- 2000,2006 ----
return true;
break;
case RTE_FUNCTION:
! if (walker(rte->funcexprs, context))
return true;
break;
case RTE_VALUES:
*************** range_table_mutator(List *rtable,
*** 2725,2731 ****
}
break;
case RTE_FUNCTION:
! MUTATE(newrte->funcexpr, rte->funcexpr, Node *);
break;
case RTE_VALUES:
MUTATE(newrte->values_lists, rte->values_lists, List *);
--- 2725,2731 ----
}
break;
case RTE_FUNCTION:
! MUTATE(newrte->funcexprs, rte->funcexprs, List *);
break;
case RTE_VALUES:
MUTATE(newrte->values_lists, rte->values_lists, List *);
*************** raw_expression_tree_walker(Node *node,
*** 3035,3040 ****
--- 3035,3045 ----
if (walker(fcall->over, context))
return true;
/* function name is deemed uninteresting */
+
+ /*
+ * RangeFunction doesn't recurse into coldeflist so we don't
+ * either
+ */
}
break;
case T_NamedArgExpr:
*************** raw_expression_tree_walker(Node *node,
*** 3113,3119 ****
{
RangeFunction *rf = (RangeFunction *) node;
! if (walker(rf->funccallnode, context))
return true;
if (walker(rf->alias, context))
return true;
--- 3118,3124 ----
{
RangeFunction *rf = (RangeFunction *) node;
! if (walker(rf->funccallnodes, context))
return true;
if (walker(rf->alias, context))
return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index b39927e..30156b0 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outSubqueryScan(StringInfo str, const S
*** 512,527 ****
static void
_outFunctionScan(StringInfo str, const FunctionScan *node)
{
WRITE_NODE_TYPE("FUNCTIONSCAN");
_outScanInfo(str, (const Scan *) node);
! WRITE_NODE_FIELD(funcexpr);
WRITE_NODE_FIELD(funccolnames);
- WRITE_NODE_FIELD(funccoltypes);
- WRITE_NODE_FIELD(funccoltypmods);
- WRITE_NODE_FIELD(funccolcollations);
WRITE_BOOL_FIELD(funcordinality);
}
static void
--- 512,533 ----
static void
_outFunctionScan(StringInfo str, const FunctionScan *node)
{
+ ListCell *lc;
+
WRITE_NODE_TYPE("FUNCTIONSCAN");
_outScanInfo(str, (const Scan *) node);
! WRITE_NODE_FIELD(funcexprs);
WRITE_NODE_FIELD(funccolnames);
WRITE_BOOL_FIELD(funcordinality);
+
+ appendStringInfoString(str, " :funcparams");
+ foreach(lc, node->funcparams)
+ {
+ appendStringInfoChar(str, ' ');
+ _outBitmapset(str, lfirst(lc));
+ }
}
static void
*************** _outFuncExpr(StringInfo str, const FuncE
*** 1012,1017 ****
--- 1018,1027 ----
WRITE_OID_FIELD(funccollid);
WRITE_OID_FIELD(inputcollid);
WRITE_NODE_FIELD(args);
+ WRITE_NODE_FIELD(funccolnames);
+ WRITE_NODE_FIELD(funccoltypes);
+ WRITE_NODE_FIELD(funccoltypmods);
+ WRITE_NODE_FIELD(funccolcollations);
WRITE_LOCATION_FIELD(location);
}
*************** _outFuncCall(StringInfo str, const FuncC
*** 2092,2097 ****
--- 2102,2108 ----
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
WRITE_NODE_FIELD(over);
+ WRITE_NODE_FIELD(coldeflist);
WRITE_LOCATION_FIELD(location);
}
*************** _outRangeTblEntry(StringInfo str, const
*** 2382,2391 ****
WRITE_NODE_FIELD(joinaliasvars);
break;
case RTE_FUNCTION:
! WRITE_NODE_FIELD(funcexpr);
! WRITE_NODE_FIELD(funccoltypes);
! WRITE_NODE_FIELD(funccoltypmods);
! WRITE_NODE_FIELD(funccolcollations);
WRITE_BOOL_FIELD(funcordinality);
break;
case RTE_VALUES:
--- 2393,2399 ----
WRITE_NODE_FIELD(joinaliasvars);
break;
case RTE_FUNCTION:
! WRITE_NODE_FIELD(funcexprs);
WRITE_BOOL_FIELD(funcordinality);
break;
case RTE_VALUES:
*************** _outRangeFunction(StringInfo str, const
*** 2621,2629 ****
WRITE_BOOL_FIELD(ordinality);
WRITE_BOOL_FIELD(lateral);
! WRITE_NODE_FIELD(funccallnode);
WRITE_NODE_FIELD(alias);
- WRITE_NODE_FIELD(coldeflist);
}
static void
--- 2629,2637 ----
WRITE_BOOL_FIELD(ordinality);
WRITE_BOOL_FIELD(lateral);
! WRITE_BOOL_FIELD(is_table);
! WRITE_NODE_FIELD(funccallnodes);
WRITE_NODE_FIELD(alias);
}
static void
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index d325bb3..c0f16bf 100644
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
*************** _readFuncExpr(void)
*** 562,567 ****
--- 562,571 ----
READ_OID_FIELD(funccollid);
READ_OID_FIELD(inputcollid);
READ_NODE_FIELD(args);
+ READ_NODE_FIELD(funccolnames);
+ READ_NODE_FIELD(funccoltypes);
+ READ_NODE_FIELD(funccoltypmods);
+ READ_NODE_FIELD(funccolcollations);
READ_LOCATION_FIELD(location);
READ_DONE();
*************** _readRangeTblEntry(void)
*** 1220,1229 ****
READ_NODE_FIELD(joinaliasvars);
break;
case RTE_FUNCTION:
! READ_NODE_FIELD(funcexpr);
! READ_NODE_FIELD(funccoltypes);
! READ_NODE_FIELD(funccoltypmods);
! READ_NODE_FIELD(funccolcollations);
READ_BOOL_FIELD(funcordinality);
break;
case RTE_VALUES:
--- 1224,1230 ----
READ_NODE_FIELD(joinaliasvars);
break;
case RTE_FUNCTION:
! READ_NODE_FIELD(funcexprs);
READ_BOOL_FIELD(funcordinality);
break;
case RTE_VALUES:
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index bfd3809..a22dac0 100644
*** a/src/backend/optimizer/path/allpaths.c
--- b/src/backend/optimizer/path/allpaths.c
***************
*** 37,42 ****
--- 37,43 ----
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
+ #include "catalog/pg_opfamily.h"
/* These parameters are set by GUC */
*************** static void
*** 1258,1263 ****
--- 1259,1265 ----
set_function_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
Relids required_outer;
+ List *pathkeys = NIL;
/*
* We don't support pushing join clauses into the quals of a function
*************** set_function_pathlist(PlannerInfo *root,
*** 1266,1273 ****
*/
required_outer = rel->lateral_relids;
/* Generate appropriate path */
! add_path(rel, create_functionscan_path(root, rel, required_outer));
/* Select cheapest path (pretty easy in this case...) */
set_cheapest(rel);
--- 1268,1325 ----
*/
required_outer = rel->lateral_relids;
+ /*
+ * The result is treated as unordered unless ORDINALITY was used, in which
+ * case it is ordered by the ordinal column (last).
+ */
+ if (rte->funcordinality)
+ {
+ ListCell *lc;
+ Var *var = NULL;
+ AttrNumber ordattno = list_length(rte->eref->colnames);
+
+ /*
+ * Find corresponding Var in our tlist by searching for matching
+ * attno.
+ */
+
+ foreach(lc, rel->reltargetlist)
+ {
+ Var *node = lfirst(lc);
+
+ if (IsA(node, Var)
+ &&node->varno == rel->relid
+ && node->varattno == ordattno
+ && node->varlevelsup == 0)
+ {
+ var = node;
+ break;
+ }
+ }
+
+ /*
+ * The Var might not be found in the tlist, but that should only
+ * happen if the ordinality column is never referenced anywhere in the
+ * query - in which case nobody can possibly care about the ordering
+ * of it. So just leave the pathkeys NIL in that case.
+ *
+ * Also, build_expression_pathkey will only build the pathkey if
+ * there's already an equivalence class; if there isn't, it indicates
+ * that nothing cares about the ordering.
+ */
+
+ if (var)
+ {
+ Oid opno = get_opfamily_member(INTEGER_BTREE_FAM_OID,
+ var->vartype, var->vartype,
+ BTLessStrategyNumber);
+
+ pathkeys = build_expression_pathkey(root, rel, (Expr *) var, opno, false);
+ }
+ }
+
/* Generate appropriate path */
! add_path(rel, create_functionscan_path(root, rel, pathkeys, required_outer));
/* Select cheapest path (pretty easy in this case...) */
set_cheapest(rel);
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index e7f8cec..9a1588e 100644
*** a/src/backend/optimizer/path/costsize.c
--- b/src/backend/optimizer/path/costsize.c
*************** cost_functionscan(Path *path, PlannerInf
*** 1088,1094 ****
* estimates for functions tend to be, there's not a lot of point in that
* refinement right now.
*/
! cost_qual_eval_node(&exprcost, rte->funcexpr, root);
startup_cost += exprcost.startup + exprcost.per_tuple;
--- 1088,1094 ----
* estimates for functions tend to be, there's not a lot of point in that
* refinement right now.
*/
! cost_qual_eval_node(&exprcost, (Node *) rte->funcexprs, root);
startup_cost += exprcost.startup + exprcost.per_tuple;
*************** void
*** 3845,3858 ****
set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel)
{
RangeTblEntry *rte;
/* Should only be applied to base relations that are functions */
Assert(rel->relid > 0);
rte = planner_rt_fetch(rel->relid, root);
Assert(rte->rtekind == RTE_FUNCTION);
! /* Estimate number of rows the function itself will return */
! rel->tuples = expression_returns_set_rows(rte->funcexpr);
/* Now estimate number of output rows, etc */
set_baserel_size_estimates(root, rel);
--- 3845,3870 ----
set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel)
{
RangeTblEntry *rte;
+ ListCell *lc;
/* Should only be applied to base relations that are functions */
Assert(rel->relid > 0);
rte = planner_rt_fetch(rel->relid, root);
Assert(rte->rtekind == RTE_FUNCTION);
! rel->tuples = 0;
!
! /*
! * Estimate number of rows the functions will return. The rowcount of
! * result of the node is that of the largest function result.
! */
! foreach(lc, rte->funcexprs)
! {
! double ntup = expression_returns_set_rows(lfirst(lc));
!
! if (ntup > rel->tuples)
! rel->tuples = ntup;
! }
/* Now estimate number of output rows, etc */
set_baserel_size_estimates(root, rel);
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 032b2cd..9f9ec7d 100644
*** a/src/backend/optimizer/path/pathkeys.c
--- b/src/backend/optimizer/path/pathkeys.c
*************** build_index_pathkeys(PlannerInfo *root,
*** 501,506 ****
--- 501,557 ----
return retval;
}
+
+ /*
+ * build_expression_pathkey
+ * Build a pathkeys list (empty or 1 element) that describes an ordering
+ * of a single expression using a given operator
+ *
+ * The result is empty if the expression isn't already in some equivalence
+ * class.
+ *
+ * The main use for this is in declaring the ordering of ordinality
+ * columns in FunctionScan, but it's written this way to avoid making any
+ * assumptions about type.
+ */
+
+ List *
+ build_expression_pathkey(PlannerInfo *root,
+ RelOptInfo *rel,
+ Expr *expr,
+ Oid opno,
+ bool nulls_first)
+ {
+ List *pathkeys = NIL;
+ Oid opfamily,
+ opcintype;
+ int16 strategy;
+ PathKey *cpathkey;
+
+ /* Find the operator in pg_amop --- failure shouldn't happen */
+ if (!get_ordering_op_properties(opno,
+ &opfamily, &opcintype, &strategy))
+ elog(ERROR, "operator %u is not a valid ordering operator",
+ opno);
+
+ cpathkey = make_pathkey_from_sortinfo(root,
+ expr,
+ NULL, /* XXX likely wrong */
+ opfamily,
+ opcintype,
+ exprCollation((Node *) expr),
+ (strategy == BTGreaterStrategyNumber),
+ nulls_first,
+ 0,
+ rel->relids,
+ false);
+
+ if (cpathkey)
+ pathkeys = lappend(pathkeys, cpathkey);
+
+ return pathkeys;
+ }
+
/*
* convert_subquery_pathkeys
* Build a pathkeys list that describes the ordering of a subquery's
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 5947e5b..622fb37 100644
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
*************** static BitmapHeapScan *make_bitmap_heaps
*** 115,123 ****
static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
List *tidquals);
static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
! Index scanrelid, Node *funcexpr, bool ordinality,
! List *funccolnames, List *funccoltypes, List *funccoltypmods,
! List *funccolcollations);
static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
Index scanrelid, List *values_lists);
static CteScan *make_ctescan(List *qptlist, List *qpqual,
--- 115,122 ----
static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid,
List *tidquals);
static FunctionScan *make_functionscan(List *qptlist, List *qpqual,
! Index scanrelid, List *funcexprs, bool ordinality,
! List *funccolnames);
static ValuesScan *make_valuesscan(List *qptlist, List *qpqual,
Index scanrelid, List *values_lists);
static CteScan *make_ctescan(List *qptlist, List *qpqual,
*************** create_functionscan_plan(PlannerInfo *ro
*** 1709,1721 ****
FunctionScan *scan_plan;
Index scan_relid = best_path->parent->relid;
RangeTblEntry *rte;
! Node *funcexpr;
/* it should be a function base rel... */
Assert(scan_relid > 0);
rte = planner_rt_fetch(scan_relid, root);
Assert(rte->rtekind == RTE_FUNCTION);
! funcexpr = rte->funcexpr;
/* Sort clauses into best execution order */
scan_clauses = order_qual_clauses(root, scan_clauses);
--- 1708,1720 ----
FunctionScan *scan_plan;
Index scan_relid = best_path->parent->relid;
RangeTblEntry *rte;
! List *funcexprs;
/* it should be a function base rel... */
Assert(scan_relid > 0);
rte = planner_rt_fetch(scan_relid, root);
Assert(rte->rtekind == RTE_FUNCTION);
! funcexprs = rte->funcexprs;
/* Sort clauses into best execution order */
scan_clauses = order_qual_clauses(root, scan_clauses);
*************** create_functionscan_plan(PlannerInfo *ro
*** 1729,1744 ****
scan_clauses = (List *)
replace_nestloop_params(root, (Node *) scan_clauses);
/* The func expression itself could contain nestloop params, too */
! funcexpr = replace_nestloop_params(root, funcexpr);
}
scan_plan = make_functionscan(tlist, scan_clauses, scan_relid,
! funcexpr,
rte->funcordinality,
! rte->eref->colnames,
! rte->funccoltypes,
! rte->funccoltypmods,
! rte->funccolcollations);
copy_path_costsize(&scan_plan->scan.plan, best_path);
--- 1728,1740 ----
scan_clauses = (List *)
replace_nestloop_params(root, (Node *) scan_clauses);
/* The func expression itself could contain nestloop params, too */
! funcexprs = (List *) replace_nestloop_params(root, (Node *) funcexprs);
}
scan_plan = make_functionscan(tlist, scan_clauses, scan_relid,
! funcexprs,
rte->funcordinality,
! rte->eref->colnames);
copy_path_costsize(&scan_plan->scan.plan, best_path);
*************** static FunctionScan *
*** 3388,3399 ****
make_functionscan(List *qptlist,
List *qpqual,
Index scanrelid,
! Node *funcexpr,
bool ordinality,
! List *funccolnames,
! List *funccoltypes,
! List *funccoltypmods,
! List *funccolcollations)
{
FunctionScan *node = makeNode(FunctionScan);
Plan *plan = &node->scan.plan;
--- 3384,3392 ----
make_functionscan(List *qptlist,
List *qpqual,
Index scanrelid,
! List *funcexprs,
bool ordinality,
! List *funccolnames)
{
FunctionScan *node = makeNode(FunctionScan);
Plan *plan = &node->scan.plan;
*************** make_functionscan(List *qptlist,
*** 3404,3415 ****
plan->lefttree = NULL;
plan->righttree = NULL;
node->scan.scanrelid = scanrelid;
! node->funcexpr = funcexpr;
node->funcordinality = ordinality;
node->funccolnames = funccolnames;
! node->funccoltypes = funccoltypes;
! node->funccoltypmods = funccoltypmods;
! node->funccolcollations = funccolcollations;
return node;
}
--- 3397,3407 ----
plan->lefttree = NULL;
plan->righttree = NULL;
node->scan.scanrelid = scanrelid;
! node->funcexprs = funcexprs;
node->funcordinality = ordinality;
node->funccolnames = funccolnames;
! /* finalize_plan will fill this in if need be */
! node->funcparams = NIL;
return node;
}
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 04a399e..633fffa 100644
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
*************** extract_lateral_references(PlannerInfo *
*** 307,313 ****
if (rte->rtekind == RTE_SUBQUERY)
vars = pull_vars_of_level((Node *) rte->subquery, 1);
else if (rte->rtekind == RTE_FUNCTION)
! vars = pull_vars_of_level(rte->funcexpr, 0);
else if (rte->rtekind == RTE_VALUES)
vars = pull_vars_of_level((Node *) rte->values_lists, 0);
else
--- 307,313 ----
if (rte->rtekind == RTE_SUBQUERY)
vars = pull_vars_of_level((Node *) rte->subquery, 1);
else if (rte->rtekind == RTE_FUNCTION)
! vars = pull_vars_of_level((Node *) rte->funcexprs, 0);
else if (rte->rtekind == RTE_VALUES)
vars = pull_vars_of_level((Node *) rte->values_lists, 0);
else
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index d8aa35d..7f39d13 100644
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
*************** subquery_planner(PlannerGlobal *glob, Qu
*** 487,493 ****
{
/* Preprocess the function expression fully */
kind = rte->lateral ? EXPRKIND_RTFUNC_LATERAL : EXPRKIND_RTFUNC;
! rte->funcexpr = preprocess_expression(root, rte->funcexpr, kind);
}
else if (rte->rtekind == RTE_VALUES)
{
--- 487,493 ----
{
/* Preprocess the function expression fully */
kind = rte->lateral ? EXPRKIND_RTFUNC_LATERAL : EXPRKIND_RTFUNC;
! rte->funcexprs = (List *) preprocess_expression(root, (Node *) rte->funcexprs, kind);
}
else if (rte->rtekind == RTE_VALUES)
{
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index b78d727..ae72a7c 100644
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
*************** add_rte_to_flat_rtable(PlannerGlobal *gl
*** 381,390 ****
/* zap unneeded sub-structure */
newrte->subquery = NULL;
newrte->joinaliasvars = NIL;
! newrte->funcexpr = NULL;
! newrte->funccoltypes = NIL;
! newrte->funccoltypmods = NIL;
! newrte->funccolcollations = NIL;
newrte->values_lists = NIL;
newrte->values_collations = NIL;
newrte->ctecoltypes = NIL;
--- 381,387 ----
/* zap unneeded sub-structure */
newrte->subquery = NULL;
newrte->joinaliasvars = NIL;
! newrte->funcexprs = NULL;
newrte->values_lists = NIL;
newrte->values_collations = NIL;
newrte->ctecoltypes = NIL;
*************** set_plan_refs(PlannerInfo *root, Plan *p
*** 525,532 ****
fix_scan_list(root, splan->scan.plan.targetlist, rtoffset);
splan->scan.plan.qual =
fix_scan_list(root, splan->scan.plan.qual, rtoffset);
! splan->funcexpr =
! fix_scan_expr(root, splan->funcexpr, rtoffset);
}
break;
case T_ValuesScan:
--- 522,529 ----
fix_scan_list(root, splan->scan.plan.targetlist, rtoffset);
splan->scan.plan.qual =
fix_scan_list(root, splan->scan.plan.qual, rtoffset);
! splan->funcexprs =
! fix_scan_list(root, splan->funcexprs, rtoffset);
}
break;
case T_ValuesScan:
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 0df70c4..64b1519 100644
*** a/src/backend/optimizer/plan/subselect.c
--- b/src/backend/optimizer/plan/subselect.c
*************** finalize_plan(PlannerInfo *root, Plan *p
*** 2135,2143 ****
break;
case T_FunctionScan:
! finalize_primnode(((FunctionScan *) plan)->funcexpr,
! &context);
! context.paramids = bms_add_members(context.paramids, scan_params);
break;
case T_ValuesScan:
--- 2135,2173 ----
break;
case T_FunctionScan:
! {
! finalize_primnode_context funccontext;
! FunctionScan *fscan = (FunctionScan *) plan;
! ListCell *lc;
!
! /*
! * Call finalize_primnode independently on each funcexpr so
! * that we can record which params are referenced in each, in
! * order to decide which need re-evaluating.
! */
!
! funccontext = context;
!
! Assert(fscan->funcparams == NIL);
!
! foreach(lc, fscan->funcexprs)
! {
! funccontext.paramids = NULL;
!
! finalize_primnode(lfirst(lc), &funccontext);
!
! /* add the function's params to the overall set */
! context.paramids = bms_add_members(context.paramids,
! funccontext.paramids);
!
! /* hand off the function's params to the node's list */
! fscan->funcparams = lappend(fscan->funcparams,
! funccontext.paramids);
! }
!
! context.paramids = bms_add_members(context.paramids,
! scan_params);
! }
break;
case T_ValuesScan:
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index c742cc9..ec0a852 100644
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
*************** inline_set_returning_functions(PlannerIn
*** 580,589 ****
/* Successful expansion, replace the rtable entry */
rte->rtekind = RTE_SUBQUERY;
rte->subquery = funcquery;
! rte->funcexpr = NULL;
! rte->funccoltypes = NIL;
! rte->funccoltypmods = NIL;
! rte->funccolcollations = NIL;
}
}
}
--- 580,586 ----
/* Successful expansion, replace the rtable entry */
rte->rtekind = RTE_SUBQUERY;
rte->subquery = funcquery;
! rte->funcexprs = NULL;
}
}
}
*************** replace_vars_in_jointree(Node *jtnode,
*** 1623,1630 ****
context);
break;
case RTE_FUNCTION:
! rte->funcexpr =
! pullup_replace_vars(rte->funcexpr,
context);
break;
case RTE_VALUES:
--- 1620,1627 ----
context);
break;
case RTE_FUNCTION:
! rte->funcexprs = (List *)
! pullup_replace_vars((Node *) rte->funcexprs,
context);
break;
case RTE_VALUES:
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 7ce8a9d..54e295e 100644
*** a/src/backend/optimizer/util/clauses.c
--- b/src/backend/optimizer/util/clauses.c
*************** static Expr *simplify_function(Oid funci
*** 111,116 ****
--- 111,117 ----
Oid result_type, int32 result_typmod,
Oid result_collid, Oid input_collid, List **args_p,
bool funcvariadic, bool process_args, bool allow_non_const,
+ FuncExpr *orig_funcexpr,
eval_const_expressions_context *context);
static List *expand_function_arguments(List *args, Oid result_type,
HeapTuple func_tuple);
*************** eval_const_expressions_mutator(Node *nod
*** 2359,2364 ****
--- 2360,2366 ----
expr->funcvariadic,
true,
true,
+ expr,
context);
if (simple) /* successfully simplified it */
return (Node *) simple;
*************** eval_const_expressions_mutator(Node *nod
*** 2378,2383 ****
--- 2380,2389 ----
newexpr->funccollid = expr->funccollid;
newexpr->inputcollid = expr->inputcollid;
newexpr->args = args;
+ newexpr->funccolnames = expr->funccolnames;
+ newexpr->funccoltypes = expr->funccoltypes;
+ newexpr->funccoltypmods = expr->funccoltypmods;
+ newexpr->funccolcollations = expr->funccolcollations;
newexpr->location = expr->location;
return (Node *) newexpr;
}
*************** eval_const_expressions_mutator(Node *nod
*** 2406,2411 ****
--- 2412,2418 ----
false,
true,
true,
+ NULL,
context);
if (simple) /* successfully simplified it */
return (Node *) simple;
*************** eval_const_expressions_mutator(Node *nod
*** 2510,2515 ****
--- 2517,2523 ----
false,
false,
false,
+ NULL,
context);
if (simple) /* successfully simplified it */
{
*************** eval_const_expressions_mutator(Node *nod
*** 2714,2719 ****
--- 2722,2728 ----
false,
true,
true,
+ NULL,
context);
if (simple) /* successfully simplified output fn */
{
*************** eval_const_expressions_mutator(Node *nod
*** 2746,2751 ****
--- 2755,2761 ----
false,
false,
true,
+ NULL,
context);
if (simple) /* successfully simplified input fn */
return (Node *) simple;
*************** simplify_boolean_equality(Oid opno, List
*** 3597,3603 ****
* polymorphic functions), result typmod, result collation, the input
* collation to use for the function, the original argument list (not
* const-simplified yet, unless process_args is false), and some flags;
! * also the context data for eval_const_expressions.
*
* Returns a simplified expression if successful, or NULL if cannot
* simplify the function call.
--- 3607,3616 ----
* polymorphic functions), result typmod, result collation, the input
* collation to use for the function, the original argument list (not
* const-simplified yet, unless process_args is false), and some flags;
! * also the context data for eval_const_expressions. The original funcexpr,
! * if there was one, is passed in too so that fields of FuncExpr which are
! * not interesting for simplification are nonetheless available to the
! * transform function; currently that applies to the coldef list fields.
*
* Returns a simplified expression if successful, or NULL if cannot
* simplify the function call.
*************** static Expr *
*** 3614,3619 ****
--- 3627,3633 ----
simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
Oid result_collid, Oid input_collid, List **args_p,
bool funcvariadic, bool process_args, bool allow_non_const,
+ FuncExpr *orig_funcexpr,
eval_const_expressions_context *context)
{
List *args = *args_p;
*************** simplify_function(Oid funcid, Oid result
*** 3679,3684 ****
--- 3693,3712 ----
fexpr.funccollid = result_collid;
fexpr.inputcollid = input_collid;
fexpr.args = args;
+ if (orig_funcexpr)
+ {
+ fexpr.funccolnames = orig_funcexpr->funccolnames;
+ fexpr.funccoltypes = orig_funcexpr->funccoltypes;
+ fexpr.funccoltypmods = orig_funcexpr->funccoltypmods;
+ fexpr.funccolcollations = orig_funcexpr->funccolcollations;
+ }
+ else
+ {
+ fexpr.funccolnames = NIL;
+ fexpr.funccoltypes = NIL;
+ fexpr.funccoltypmods = NIL;
+ fexpr.funccolcollations = NIL;
+ }
fexpr.location = -1;
newexpr = (Expr *)
*************** inline_set_returning_function(PlannerInf
*** 4541,4551 ****
if (rte->funcordinality)
return NULL;
! /* Fail if FROM item isn't a simple FuncExpr */
! fexpr = (FuncExpr *) rte->funcexpr;
! if (fexpr == NULL || !IsA(fexpr, FuncExpr))
return NULL;
func_oid = fexpr->funcid;
/*
--- 4569,4581 ----
if (rte->funcordinality)
return NULL;
! /* Fail if FROM item isn't a simple, single, FuncExpr */
! if (list_length(rte->funcexprs) != 1
! || !IsA(linitial(rte->funcexprs), FuncExpr))
return NULL;
+ fexpr = (FuncExpr *) linitial(rte->funcexprs);
+
func_oid = fexpr->funcid;
/*
*************** inline_set_returning_function(PlannerInf
*** 4726,4740 ****
/*
* If it returns RECORD, we have to check against the column type list
! * provided in the RTE; check_sql_fn_retval can't do that. (If no match,
! * we just fail to inline, rather than complaining; see notes for
! * tlist_matches_coltypelist.) We don't have to do this for functions
! * with declared OUT parameters, even though their funcresulttype is
! * RECORDOID, so check get_func_result_type too.
*/
if (fexpr->funcresulttype == RECORDOID &&
get_func_result_type(func_oid, NULL, NULL) == TYPEFUNC_RECORD &&
! !tlist_matches_coltypelist(querytree->targetList, rte->funccoltypes))
goto fail;
/*
--- 4756,4770 ----
/*
* If it returns RECORD, we have to check against the column type list
! * provided in the FuncExpr (used to be in the RTE); check_sql_fn_retval
! * can't do that. (If no match, we just fail to inline, rather than
! * complaining; see notes for tlist_matches_coltypelist.) We don't have to
! * do this for functions with declared OUT parameters, even though their
! * funcresulttype is RECORDOID, so check get_func_result_type too.
*/
if (fexpr->funcresulttype == RECORDOID &&
get_func_result_type(func_oid, NULL, NULL) == TYPEFUNC_RECORD &&
! !tlist_matches_coltypelist(querytree->targetList, fexpr->funccoltypes))
goto fail;
/*
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 64b1705..55b9f56 100644
*** a/src/backend/optimizer/util/pathnode.c
--- b/src/backend/optimizer/util/pathnode.c
*************** create_subqueryscan_path(PlannerInfo *ro
*** 1623,1628 ****
--- 1623,1629 ----
*/
Path *
create_functionscan_path(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys,
Relids required_outer)
{
Path *pathnode = makeNode(Path);
*************** create_functionscan_path(PlannerInfo *ro
*** 1631,1637 ****
pathnode->parent = rel;
pathnode->param_info = get_baserel_parampathinfo(root, rel,
required_outer);
! pathnode->pathkeys = NIL; /* for now, assume unordered result */
cost_functionscan(pathnode, root, rel, pathnode->param_info);
--- 1632,1638 ----
pathnode->parent = rel;
pathnode->param_info = get_baserel_parampathinfo(root, rel,
required_outer);
! pathnode->pathkeys = pathkeys;
cost_functionscan(pathnode, root, rel, pathnode->param_info);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 11f6291..8906b44 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static void doNegateFloat(Value *v);
*** 153,158 ****
--- 153,159 ----
static Node *makeAArrayExpr(List *elements, int location);
static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args,
List *args, int location);
+ static void processTableFuncColdef(RangeFunction *n, List *coldeflist, int location, core_yyscan_t yyscanner);
static List *mergeTableFuncParameters(List *func_args, List *columns);
static TypeName *TableFuncTypeName(List *columns);
static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_t yyscanner);
*************** static Node *makeRecursiveViewSelect(cha
*** 404,411 ****
%type <defelt> def_elem reloption_elem old_aggr_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el
! columnref in_expr having_clause func_table array_expr
ExclusionWhereClause
%type <list> ExclusionConstraintList ExclusionConstraintElem
%type <list> func_arg_list
%type <node> func_arg_expr
--- 405,417 ----
%type <defelt> def_elem reloption_elem old_aggr_elem
%type <node> def_arg columnElem where_clause where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el
! columnref in_expr having_clause array_expr
ExclusionWhereClause
+ %type <list> func_table_def
+ %type <list> func_table_list
+ %type <list> func_table_single
+ %type <list> opt_col_def_list
+ %type <boolean> opt_ordinality
%type <list> ExclusionConstraintList ExclusionConstraintElem
%type <list> func_arg_list
%type <node> func_arg_expr
*************** static Node *makeRecursiveViewSelect(cha
*** 420,426 ****
%type <list> func_alias_clause
%type <sortby> sortby
%type <ielem> index_elem
! %type <node> table_ref
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
--- 426,432 ----
%type <list> func_alias_clause
%type <sortby> sortby
%type <ielem> index_elem
! %type <node> table_ref func_table_ref
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
*************** from_list:
*** 9638,9690 ****
| from_list ',' table_ref { $$ = lappend($1, $3); }
;
/*
* table_ref is where an alias clause can be attached.
*/
table_ref: relation_expr opt_alias_clause
{
$1->alias = $2;
$$ = (Node *) $1;
}
! | func_table func_alias_clause
! {
! RangeFunction *n = makeNode(RangeFunction);
! n->lateral = false;
! n->ordinality = false;
! n->funccallnode = $1;
! n->alias = linitial($2);
! n->coldeflist = lsecond($2);
! $$ = (Node *) n;
! }
! | func_table WITH_ORDINALITY func_alias_clause
! {
! RangeFunction *n = makeNode(RangeFunction);
! n->lateral = false;
! n->ordinality = true;
! n->funccallnode = $1;
! n->alias = linitial($3);
! n->coldeflist = lsecond($3);
! $$ = (Node *) n;
! }
! | LATERAL_P func_table func_alias_clause
{
! RangeFunction *n = makeNode(RangeFunction);
! n->lateral = true;
! n->ordinality = false;
! n->funccallnode = $2;
! n->alias = linitial($3);
! n->coldeflist = lsecond($3);
! $$ = (Node *) n;
}
! | LATERAL_P func_table WITH_ORDINALITY func_alias_clause
{
! RangeFunction *n = makeNode(RangeFunction);
! n->lateral = true;
! n->ordinality = true;
! n->funccallnode = $2;
! n->alias = linitial($4);
! n->coldeflist = lsecond($4);
! $$ = (Node *) n;
}
| select_with_parens opt_alias_clause
{
--- 9644,9672 ----
| from_list ',' table_ref { $$ = lappend($1, $3); }
;
+ opt_ordinality: WITH_ORDINALITY { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
/*
* table_ref is where an alias clause can be attached.
+ *
+ * func_table is a list whose first element is a list of FuncCall nodes,
+ * and which has a second element iff the TABLE() syntax was used.
*/
table_ref: relation_expr opt_alias_clause
{
$1->alias = $2;
$$ = (Node *) $1;
}
! | func_table_ref
{
! $$ = (Node *) $1;
}
! | LATERAL_P func_table_ref
{
! ((RangeFunction *) $2)->lateral = true;
! $$ = $2;
}
| select_with_parens opt_alias_clause
{
*************** table_ref: relation_expr opt_alias_claus
*** 9757,9762 ****
--- 9739,9793 ----
}
;
+ /*----------
+ * func_table_ref represents a function invocation in FROM list. It can be a
+ * plain function call, like "FROM foo(...)", or a TABLE expression with one
+ * or more function calls, "FROM TABLE (foo(...), bar(...))".
+ *
+ * In the TABLE syntax, a column definition list can be given for each
+ * function. For example:
+ *
+ * FROM TABLE (foo() AS (foo_res_a text, foo_res_b text),
+ * bar() AS (bar_res_a text, bar_res_b text)) AS alias
+ *
+ * In the plain function call syntax, a column definition list can be given
+ * like this:
+ *
+ * FROM foo() AS alias (foo_res_a text, foo_res_b text)
+ *
+ * For backwards-compatibility, that is also allowed with the TABLE syntax,
+ * if the TABLE-expression contains only one function call:
+ *
+ * FROM TABLE(foo()) AS alias (foo_res_a text, foo_res_b text)"
+ *
+ *----------
+ */
+ func_table_ref: func_table_single opt_ordinality func_alias_clause
+ {
+ RangeFunction *n = makeNode(RangeFunction);
+
+ n->ordinality = $2;
+ n->is_table = false;
+ n->funccallnodes = $1;
+ n->alias = linitial($3);
+
+ processTableFuncColdef(n, lsecond($3), @3, yyscanner);
+
+ $$ = (Node *) n;
+ }
+ | TABLE '(' func_table_list ')' opt_ordinality func_alias_clause
+ {
+ RangeFunction *n = makeNode(RangeFunction);
+ n->ordinality = $5;
+ n->is_table = true;
+ n->funccallnodes = $3;
+ n->alias = linitial($6);
+
+ processTableFuncColdef(n, lsecond($6), @3, yyscanner);
+
+ $$ = (Node *) n;
+ }
+ ;
/*
* It may seem silly to separate joined_table from table_ref, but there is
*************** relation_expr_opt_alias: relation_expr
*** 9996,10004 ****
}
;
! func_table: func_expr_windowless { $$ = $1; }
;
where_clause:
WHERE a_expr { $$ = $2; }
--- 10027,10127 ----
}
;
! func_table_list: func_table_def { $$ = $1; }
! | func_table_list ',' func_table_def { $$ = list_concat($1, $3); }
! ;
!
! func_table_def: func_table_single opt_col_def_list
! {
! if (list_length($2) > 0)
! {
! List *l = $1;
!
! if (list_length(l) > 1)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is not allowed for unnest with multiple
arguments"),
! errhint("Use separate unnest calls with one argument each"),
! parser_errposition(@2)));
!
! /*
! * A column definition list is only allowed for functions
! * returning 'record', but that is checked in later, in
! * parse analysis, as we don't know the result type yet.
! * We do check that it's a regular FuncCall, though.
! * (it could be some other expression that looks like
! * a function call but is handled as a separate expression
! * type, e.g COALESCE)
! */
! if (!IsA(linitial(l), FuncCall))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is not allowed for this expression"),
! parser_errposition(@2)));
!
! ((FuncCall *) linitial(l))->coldeflist = $2;
! }
! $$ = $1;
! }
;
+ /*
+ * All table function calls in FROM come through here so that we can do the
+ * expansion of unnest().
+ */
+ func_table_single: func_expr_windowless
+ {
+ /*----------
+ * Handle the spec's UNNEST syntax, by transforming
+ *
+ * UNNEST(a, b, ...)
+ *
+ * into
+ *
+ * TABLE (UNNEST(a), UNNEST(b), ...)
+ *
+ * We handle this here, rather than directly as grammar
+ * rules, to avoid interfering with the use of unnest()
+ * as a plain SRF in other contexts. Ugly, but effective.
+ *
+ * Note, strcmp not pg_strcasecmp, identifiers have
+ * already been casefolded.
+ *----------
+ */
+ if (IsA($1, FuncCall) &&
+ list_length(((FuncCall *) $1)->funcname) == 1 &&
+ strcmp(strVal(linitial(((FuncCall *) $1)->funcname)), "unnest") == 0)
+ {
+ FuncCall *fc = (FuncCall *) $1;
+ List *funccalls = NIL;
+ ListCell *lc;
+
+ if (fc->agg_order != NIL || fc->func_variadic || fc->agg_star || fc->agg_distinct)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid syntax for unnest()"),
+ parser_errposition(@1)));
+
+ foreach(lc, fc->args)
+ {
+ funccalls = lappend(funccalls,
+ makeFuncCall(SystemFuncName("unnest"),
+ list_make1(lfirst(lc)),
+ fc->location));
+ }
+
+ $$ = funccalls;
+ }
+ else
+ {
+ $$ = list_make1($1);
+ }
+ }
+ ;
+
+ opt_col_def_list: AS '(' TableFuncElementList ')' { $$ = $3; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
where_clause:
WHERE a_expr { $$ = $2; }
*************** makeXmlExpr(XmlExprOp op, char *name, Li
*** 13396,13401 ****
--- 13519,13569 ----
}
/*
+ * Support the old column definition list syntax, either when TABLE() was
+ * not used, or when TABLE(func()) was used with only one function.
+ *
+ * This handles pushing the coldeflist down into the function call node.
+ */
+ static void
+ processTableFuncColdef(RangeFunction *n, List *coldeflist,
+ int location, core_yyscan_t yyscanner)
+ {
+ /*
+ * coldeflist is allowed only for exactly one function; if more than one,
+ * then the coldeflist must be applied inside TABLE() not outside.
+ */
+ if (coldeflist != NIL)
+ {
+ FuncCall *fn = linitial(n->funccallnodes);
+
+ if (list_length(n->funccallnodes) > 1)
+ {
+ if (n->is_table)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("a column definition list is not allowed for TABLE() with multiple functions"),
+ errhint("Give column definition lists for individual functions inside TABLE()"),
+ parser_errposition(location)));
+ }
+
+ if (!IsA(fn, FuncCall))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("a column definition list is not allowed for this expression"),
+ parser_errposition(location)));
+
+ if (fn->coldeflist != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple column definition lists are not allowed for the same function"),
+ errhint("remove one of the definition lists"),
+ parser_errposition(location)));
+
+ fn->coldeflist = coldeflist;
+ }
+ }
+
+ /*
* Merge the input and output parameters of a table function.
*/
static List *
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7a1261d..64b69bc 100644
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
***************
*** 24,29 ****
--- 24,30 ----
#include "optimizer/tlist.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
+ #include "parser/parser.h"
#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
#include "parser/parse_collate.h"
*************** transformRangeSubselect(ParseState *psta
*** 515,532 ****
static RangeTblEntry *
transformRangeFunction(ParseState *pstate, RangeFunction *r)
{
! Node *funcexpr;
! char *funcname;
bool is_lateral;
RangeTblEntry *rte;
!
! /*
! * Get function name for possible use as alias. We use the same
! * transformation rules as for a SELECT output expression. For a FuncCall
! * node, the result will be the function name, but it is possible for the
! * grammar to hand back other node types.
! */
! funcname = FigureColname(r->funccallnode);
/*
* We make lateral_only names of this level visible, whether or not the
--- 516,526 ----
static RangeTblEntry *
transformRangeFunction(ParseState *pstate, RangeFunction *r)
{
! List *funcexprs = NIL;
! List *funcnames = NIL;
bool is_lateral;
RangeTblEntry *rte;
! ListCell *lc;
/*
* We make lateral_only names of this level visible, whether or not the
*************** transformRangeFunction(ParseState *pstat
*** 541,586 ****
pstate->p_lateral_active = true;
/*
! * Transform the raw expression.
*/
! funcexpr = transformExpr(pstate, r->funccallnode, EXPR_KIND_FROM_FUNCTION);
pstate->p_lateral_active = false;
/*
! * We must assign collations now so that we can fill funccolcollations.
*/
! assign_expr_collations(pstate, funcexpr);
/*
* Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
* there are any lateral cross-references in it.
*/
! is_lateral = r->lateral || contain_vars_of_level(funcexpr, 0);
/*
* OK, build an RTE for the function.
*/
! rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
r, is_lateral, true);
- /*
- * If a coldeflist was supplied, ensure it defines a legal set of names
- * (no duplicates) and datatypes (no pseudo-types, for instance).
- * addRangeTableEntryForFunction looked up the type names but didn't check
- * them further than that.
- */
- if (r->coldeflist)
- {
- TupleDesc tupdesc;
-
- tupdesc = BuildDescFromLists(rte->eref->colnames,
- rte->funccoltypes,
- rte->funccoltypmods,
- rte->funccolcollations);
- CheckAttributeNamesTypes(tupdesc, RELKIND_COMPOSITE_TYPE, false);
- }
-
return rte;
}
--- 535,586 ----
pstate->p_lateral_active = true;
/*
! * Transform the raw expressions.
! *
! * While transforming, get function names for possible use as alias and
! * column names. We use the same transformation rules as for a SELECT
! * output expression. For a FuncCall node, the result will be the function
! * name, but it is possible for the grammar to hand back other node types.
! *
! * Have to get this info now, because FigureColname only works on raw
! * parsetree. Actually deciding what to do with the names is left up to
! * addRangeTableEntryForFunction (which does not see the raw parsenodes).
*/
!
! foreach(lc, r->funccallnodes)
! {
! Node *node = lfirst(lc);
!
! funcexprs = lappend(funcexprs,
! transformExpr(pstate, node, EXPR_KIND_FROM_FUNCTION));
!
! funcnames = lappend(funcnames, makeString(FigureColname(node)));
! }
pstate->p_lateral_active = false;
/*
! * Assign collations now.
! *
! * This comment used to say that this was required to fill in
! * funccolcollations, but that does not appear to have been the case
! * (assignment of funccolcollations was since moved to the Expr handling
! * above)
*/
! assign_list_collations(pstate, funcexprs);
/*
* Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
* there are any lateral cross-references in it.
*/
! is_lateral = r->lateral || contain_vars_of_level((Node *) funcexprs, 0);
/*
* OK, build an RTE for the function.
*/
! rte = addRangeTableEntryForFunction(pstate, funcnames, funcexprs,
r, is_lateral, true);
return rte;
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 68b711d..5544f4a 100644
*** a/src/backend/parser/parse_expr.c
--- b/src/backend/parser/parse_expr.c
*************** transformIndirection(ParseState *pstate,
*** 463,470 ****
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
list_make1(result),
! NIL, NULL, false, false, false,
! NULL, true, location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n), location);
result = newresult;
--- 463,470 ----
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
list_make1(result),
! location,
! NULL);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n), location);
result = newresult;
*************** transformColumnRef(ParseState *pstate, C
*** 631,638 ****
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! NIL, NULL, false, false, false,
! NULL, true, cref->location);
}
break;
}
--- 631,637 ----
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! cref->location, NULL);
}
break;
}
*************** transformColumnRef(ParseState *pstate, C
*** 676,683 ****
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! NIL, NULL, false, false, false,
! NULL, true, cref->location);
}
break;
}
--- 675,681 ----
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! cref->location, NULL);
}
break;
}
*************** transformColumnRef(ParseState *pstate, C
*** 734,741 ****
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! NIL, NULL, false, false, false,
! NULL, true, cref->location);
}
break;
}
--- 732,738 ----
node = ParseFuncOrColumn(pstate,
list_make1(makeString(colname)),
list_make1(node),
! cref->location, NULL);
}
break;
}
*************** transformFuncCall(ParseState *pstate, Fu
*** 1242,1248 ****
{
List *targs;
ListCell *args;
- Expr *tagg_filter;
/* Transform the list of arguments ... */
targs = NIL;
--- 1239,1244 ----
*************** transformFuncCall(ParseState *pstate, Fu
*** 1252,1279 ****
(Node *) lfirst(args)));
}
- /*
- * Transform the aggregate filter using transformWhereClause(), to which
- * FILTER is virtually identical...
- */
- tagg_filter = NULL;
- if (fn->agg_filter != NULL)
- tagg_filter = (Expr *)
- transformWhereClause(pstate, (Node *) fn->agg_filter,
- EXPR_KIND_FILTER, "FILTER");
-
/* ... and hand off to ParseFuncOrColumn */
return ParseFuncOrColumn(pstate,
fn->funcname,
targs,
! fn->agg_order,
! tagg_filter,
! fn->agg_star,
! fn->agg_distinct,
! fn->func_variadic,
! fn->over,
! false,
! fn->location);
}
static Node *
--- 1248,1259 ----
(Node *) lfirst(args)));
}
/* ... and hand off to ParseFuncOrColumn */
return ParseFuncOrColumn(pstate,
fn->funcname,
targs,
! fn->location,
! fn);
}
static Node *
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index ede36d1..ef20f9f 100644
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
***************
*** 15,20 ****
--- 15,22 ----
#include "postgres.h"
#include "access/htup_details.h"
+ #include "catalog/heap.h"
+ #include "catalog/pg_class.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
***************
*** 22,27 ****
--- 24,30 ----
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_agg.h"
+ #include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
#include "parser/parse_func.h"
#include "parser/parse_relation.h"
*************** static Node *ParseComplexProjection(Pars
*** 61,70 ****
*/
Node *
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
! List *agg_order, Expr *agg_filter,
! bool agg_star, bool agg_distinct, bool func_variadic,
! WindowDef *over, bool is_column, int location)
{
Oid rettype;
Oid funcid;
ListCell *l;
--- 64,79 ----
*/
Node *
ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
! int location, FuncCall *fn)
{
+ List *agg_order = (fn ? fn->agg_order : NIL);
+ Expr *agg_filter = NULL;
+ bool agg_star = (fn ? fn->agg_star : false);
+ bool agg_distinct = (fn ? fn->agg_distinct : false);
+ bool func_variadic = (fn ? fn->func_variadic : false);
+ WindowDef *over = (fn ? fn->over : NULL);
+ List *coldeflist = (fn ? fn->coldeflist : NIL);
+ bool is_column = (fn == NULL);
Oid rettype;
Oid funcid;
ListCell *l;
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 98,103 ****
--- 107,121 ----
parser_errposition(pstate, location)));
/*
+ * Transform the aggregate filter using transformWhereClause(), to which
+ * FILTER is virtually identical...
+ */
+ if (fn && fn->agg_filter != NULL)
+ agg_filter = (Expr *)
+ transformWhereClause(pstate, (Node *) fn->agg_filter,
+ EXPR_KIND_FILTER, "FILTER");
+
+ /*
* Extract arg type info in preparation for function lookup.
*
* If any arguments are Param markers of type VOID, we discard them from
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 414,419 ****
--- 432,499 ----
funcexpr->args = fargs;
funcexpr->location = location;
+ /*
+ * If we're called in the FROM-clause, we might have a column
+ * definition list if we return RECORD. The grammar should prevent
+ * supplying a list in other contexts. Missing coldeflists are checked
+ * for in parse_relation.c
+ */
+ if (coldeflist != NIL)
+ {
+ TypeFuncClass functypclass;
+ ListCell *col;
+ TupleDesc tupdesc;
+
+ Assert(pstate->p_expr_kind == EXPR_KIND_FROM_FUNCTION);
+
+ functypclass = get_func_result_type(funcid, NULL, NULL);
+
+ if (functypclass != TYPEFUNC_RECORD)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("a column definition list is only allowed for functions returning \"record\""),
+ parser_errposition(pstate, location)));
+
+ /*
+ * Use the column definition list to form the
+ * funccolnames/funccoltypes/funccoltypmods/funccolcollations
+ * lists.
+ */
+ foreach(col, coldeflist)
+ {
+ ColumnDef *n = (ColumnDef *) lfirst(col);
+ char *attrname;
+ Oid attrtype;
+ int32 attrtypmod;
+ Oid attrcollation;
+
+ attrname = pstrdup(n->colname);
+ if (n->typeName->setof)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" cannot be declared SETOF",
+ attrname),
+ parser_errposition(pstate, n->typeName->location)));
+ typenameTypeIdAndMod(pstate, n->typeName, &attrtype, &attrtypmod);
+ attrcollation = GetColumnDefCollation(pstate, n, attrtype);
+ funcexpr->funccolnames = lappend(funcexpr->funccolnames, makeString(attrname));
+ funcexpr->funccoltypes = lappend_oid(funcexpr->funccoltypes, attrtype);
+ funcexpr->funccoltypmods = lappend_int(funcexpr->funccoltypmods, attrtypmod);
+ funcexpr->funccolcollations = lappend_oid(funcexpr->funccolcollations,
+ attrcollation);
+ }
+
+ /*
+ * Ensure it defines a legal set of names (no duplicates) and
+ * datatypes (no pseudo-types, for instance).
+ */
+ tupdesc = BuildDescFromLists(funcexpr->funccolnames,
+ funcexpr->funccoltypes,
+ funcexpr->funccoltypmods,
+ funcexpr->funccolcollations);
+ CheckAttributeNamesTypes(tupdesc, RELKIND_COMPOSITE_TYPE, false);
+ }
+
retval = (Node *) funcexpr;
}
else if (fdresult == FUNCDETAIL_AGGREGATE && !over)
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 0052d21..1bc784c 100644
*** a/src/backend/parser/parse_relation.c
--- b/src/backend/parser/parse_relation.c
***************
*** 27,32 ****
--- 27,33 ----
#include "parser/parsetree.h"
#include "parser/parse_relation.h"
#include "parser/parse_type.h"
+ #include "parser/parse_target.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
*************** static void expandRelation(Oid relid, Al
*** 43,49 ****
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars);
! static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars);
--- 44,50 ----
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars);
! static void expandTupleDesc(TupleDesc tupdesc, Alias *eref, int atts_done,
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars);
*************** buildRelationAliases(TupleDesc tupdesc,
*** 880,886 ****
/* tack on the ordinality column at the end */
if (ordinality)
{
! Value *attrname;
if (aliaslc)
{
--- 881,887 ----
/* tack on the ordinality column at the end */
if (ordinality)
{
! Value *attrname;
if (aliaslc)
{
*************** buildRelationAliases(TupleDesc tupdesc,
*** 912,933 ****
* when the function returns a scalar type (not composite or RECORD).
*
* funcexpr: transformed expression tree for the function call
! * funcname: function name (used only for error message)
* alias: the user-supplied alias, or NULL if none
* eref: the eref Alias to store column names in
* ordinality: whether to add an ordinality column
*
* eref->colnames is filled in.
*
! * The caller must have previously filled in eref->aliasname, which will
! * be used as the result column name if no alias is given.
*
* A user-supplied Alias can contain up to two column alias names; one for
* the function result, and one for the ordinality column; it is an error
* to specify more aliases than required.
*/
static void
! buildScalarFunctionAlias(Node *funcexpr, char *funcname,
Alias *alias, Alias *eref, bool ordinality)
{
Assert(eref->colnames == NIL);
--- 913,942 ----
* when the function returns a scalar type (not composite or RECORD).
*
* funcexpr: transformed expression tree for the function call
! * funcname: function name
! * prefer_funcname: prefer to use funcname rather than eref->aliasname
* alias: the user-supplied alias, or NULL if none
* eref: the eref Alias to store column names in
* ordinality: whether to add an ordinality column
*
* eref->colnames is filled in.
*
! * The caller must have previously filled in eref->aliasname, which will be
! * used as the result column name if no column alias is given, no column name
! * is provided by the function, and prefer_funcname is false. (This makes FROM
! * func() AS foo use "foo" as the column name as well as the table alias.)
! *
! * prefer_funcname is true for the TABLE(func()) case, where calling the
! * resulting column "table" would be silly, and using the function name as
! * eref->aliasname would be inconsistent with TABLE(func1(),func2()). This
! * isn't ideal, but seems to be the least surprising behaviour.
*
* A user-supplied Alias can contain up to two column alias names; one for
* the function result, and one for the ordinality column; it is an error
* to specify more aliases than required.
*/
static void
! buildScalarFunctionAlias(Node *funcexpr, char *funcname, bool prefer_funcname,
Alias *alias, Alias *eref, bool ordinality)
{
Assert(eref->colnames == NIL);
*************** buildScalarFunctionAlias(Node *funcexpr,
*** 959,970 ****
* caller (which is not necessarily the function name!)
*/
if (!pname)
! pname = eref->aliasname;
eref->colnames = list_make1(makeString(pname));
}
! /* If we don't have a name for the ordinality column yet, supply a default. */
if (ordinality && list_length(eref->colnames) < 2)
eref->colnames = lappend(eref->colnames, makeString(pstrdup("ordinality")));
--- 968,982 ----
* caller (which is not necessarily the function name!)
*/
if (!pname)
! pname = (prefer_funcname ? funcname : eref->aliasname);
eref->colnames = list_make1(makeString(pname));
}
! /*
! * If we don't have a name for the ordinality column yet, supply a
! * default.
! */
if (ordinality && list_length(eref->colnames) < 2)
eref->colnames = lappend(eref->colnames, makeString(pstrdup("ordinality")));
*************** addRangeTableEntryForSubquery(ParseState
*** 1230,1243 ****
}
/*
! * Add an entry for a function to the pstate's range table (p_rtable).
*
* This is just like addRangeTableEntry() except that it makes a function RTE.
*/
RangeTblEntry *
addRangeTableEntryForFunction(ParseState *pstate,
! char *funcname,
! Node *funcexpr,
RangeFunction *rangefunc,
bool lateral,
bool inFromCl)
--- 1242,1256 ----
}
/*
! * Add an entry for a function (or functions) to the pstate's range table
! * (p_rtable).
*
* This is just like addRangeTableEntry() except that it makes a function RTE.
*/
RangeTblEntry *
addRangeTableEntryForFunction(ParseState *pstate,
! List *funcnames,
! List *funcexprs,
RangeFunction *rangefunc,
bool lateral,
bool inFromCl)
*************** addRangeTableEntryForFunction(ParseState
*** 1247,1293 ****
Oid funcrettype;
TupleDesc tupdesc;
Alias *alias = rangefunc->alias;
- List *coldeflist = rangefunc->coldeflist;
Alias *eref;
rte->rtekind = RTE_FUNCTION;
rte->relid = InvalidOid;
rte->subquery = NULL;
! rte->funcexpr = funcexpr;
! rte->funccoltypes = NIL;
! rte->funccoltypmods = NIL;
! rte->funccolcollations = NIL;
rte->alias = alias;
! eref = makeAlias(alias ? alias->aliasname : funcname, NIL);
rte->eref = eref;
/*
* Now determine if the function returns a simple or composite type.
*/
! functypclass = get_expr_result_type(funcexpr,
! &funcrettype,
! &tupdesc);
!
! /*
! * A coldeflist is required if the function returns RECORD and hasn't got
! * a predetermined record type, and is prohibited otherwise.
! */
! if (coldeflist != NIL)
{
! if (functypclass != TYPEFUNC_RECORD)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is only allowed for functions returning \"record\""),
! parser_errposition(pstate, exprLocation(funcexpr))));
}
else
{
! if (functypclass == TYPEFUNC_RECORD)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is required for functions returning \"record\""),
! parser_errposition(pstate, exprLocation(funcexpr))));
}
if (functypclass == TYPEFUNC_COMPOSITE)
--- 1260,1420 ----
Oid funcrettype;
TupleDesc tupdesc;
Alias *alias = rangefunc->alias;
Alias *eref;
+ char *aliasname;
+ Oid *funcrettypes = NULL;
+ TupleDesc *functupdescs = NULL;
+ int nfuncs = list_length(funcexprs);
+ ListCell *lc,
+ *lc2;
+ int i;
+ int j;
+ int natts;
rte->rtekind = RTE_FUNCTION;
rte->relid = InvalidOid;
rte->subquery = NULL;
! rte->funcexprs = funcexprs;
rte->alias = alias;
! /*----------
! * Choose the RTE alias name.
! *
! * We punt to "table" if the list results from explicit TABLE() syntax
! * regardless of number of functions. Otherwise, use the first function,
! * since either there is only one, or it was an unnest() which got
! * expanded. We don't currently need to record this fact in the
! * transformed node, since deparse always emits an alias for table
! * functions, and
! * ... FROM unnest(a,b)
! * and
! * ... FROM TABLE(unnest(a),unnest(b)) AS "unnest"
! * are equivalent enough for our purposes.
! *----------
! */
! if (alias)
! aliasname = alias->aliasname;
! else if (rangefunc->is_table)
! aliasname = "table";
! else
! aliasname = strVal(linitial(funcnames));
!
! eref = makeAlias(aliasname, NIL);
rte->eref = eref;
/*
* Now determine if the function returns a simple or composite type.
+ *
+ * If there's more than one function, the result must be composite, and we
+ * have to produce a merged tupdesc.
*/
! if (nfuncs == 1)
{
! functypclass = get_expr_result_type(linitial(funcexprs),
! &funcrettype,
! &tupdesc);
!
! /*
! * TYPEFUNC_RECORD is only possible here if a column definition list
! * was not supplied.
! */
! if (functypclass == TYPEFUNC_RECORD)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is required for functions returning \"record\""),
! parser_errposition(pstate, exprLocation(linitial(funcexprs)))));
}
else
{
! /*
! * Produce a flattened TupleDesc with all the constituent columns from
! * all functions. We're only going to use this for assigning aliases,
! * so we don't need collations and so on.
! *
! * This would be less painful if there was a reasonable way to insert
! * dropped columns into a tupdesc.
! */
!
! funcrettypes = palloc(nfuncs * sizeof(Oid));
! functupdescs = palloc(nfuncs * sizeof(TupleDesc));
!
! i = 0;
! natts = 0;
! forboth(lc, funcexprs, lc2, funcnames)
! {
! functypclass = get_expr_result_type(lfirst(lc),
! &funcrettypes[i],
! &functupdescs[i]);
!
! switch (functypclass)
! {
! case TYPEFUNC_RECORD:
!
! /*
! * Only gets here if no column definition list was
! * supplied for a function returning an unspecified
! * RECORD.
! */
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("a column definition list is required for functions returning \"record\""),
! parser_errposition(pstate, exprLocation(lfirst(lc)))));
!
! case TYPEFUNC_SCALAR:
! {
! FuncExpr *funcexpr = lfirst(lc);
! char *pname = NULL;
!
! /*
! * Function might have its own idea what the result
! * column name should be. Prefer that (since
! * buildScalarFunctionAlias does too)
! */
! if (IsA(funcexpr, FuncExpr))
! pname = get_func_result_name(funcexpr->funcid);
!
! /*
! * If not, use the function name as the column name.
! */
! if (!pname)
! pname = strVal(lfirst(lc2));
!
! functupdescs[i] = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(functupdescs[i],
! (AttrNumber) 1,
! pname,
! funcrettypes[i],
! -1,
! 0);
! break;
! }
!
! case TYPEFUNC_COMPOSITE:
! break;
!
! default:
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("function \"%s\" in FROM has unsupported return type %s",
! strVal(lfirst(lc2)), format_type_be(funcrettype)),
! parser_errposition(pstate, exprLocation(lfirst(lc)))));
! }
! natts += functupdescs[i]->natts;
!
! i++;
! }
!
! functypclass = TYPEFUNC_COMPOSITE;
! funcrettype = RECORDOID;
!
! /* Merge the tuple descs of each function into a composite one */
! tupdesc = CreateTemplateTupleDesc(natts, false);
! natts = 0;
! for (i = 0; i < nfuncs; i++)
! {
! for (j = 1; j <= functupdescs[i]->natts; j++)
! TupleDescCopyEntry(tupdesc, ++natts, functupdescs[i], j);
! }
}
if (functypclass == TYPEFUNC_COMPOSITE)
*************** addRangeTableEntryForFunction(ParseState
*** 1300,1351 ****
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
! buildScalarFunctionAlias(funcexpr, funcname, alias, eref, rangefunc->ordinality);
! }
! else if (functypclass == TYPEFUNC_RECORD)
! {
! ListCell *col;
!
! if (rangefunc->ordinality)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("WITH ORDINALITY is not supported for functions returning \"record\""),
! parser_errposition(pstate, exprLocation(funcexpr))));
!
! /*
! * Use the column definition list to form the alias list and
! * funccoltypes/funccoltypmods/funccolcollations lists.
! */
! foreach(col, coldeflist)
! {
! ColumnDef *n = (ColumnDef *) lfirst(col);
! char *attrname;
! Oid attrtype;
! int32 attrtypmod;
! Oid attrcollation;
!
! attrname = pstrdup(n->colname);
! if (n->typeName->setof)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
! errmsg("column \"%s\" cannot be declared SETOF",
! attrname),
! parser_errposition(pstate, n->typeName->location)));
! typenameTypeIdAndMod(pstate, n->typeName, &attrtype, &attrtypmod);
! attrcollation = GetColumnDefCollation(pstate, n, attrtype);
! eref->colnames = lappend(eref->colnames, makeString(attrname));
! rte->funccoltypes = lappend_oid(rte->funccoltypes, attrtype);
! rte->funccoltypmods = lappend_int(rte->funccoltypmods, attrtypmod);
! rte->funccolcollations = lappend_oid(rte->funccolcollations,
! attrcollation);
! }
}
else
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("function \"%s\" in FROM has unsupported return type %s",
! funcname, format_type_be(funcrettype)),
! parser_errposition(pstate, exprLocation(funcexpr))));
/*
* Set flags and access permissions.
--- 1427,1442 ----
else if (functypclass == TYPEFUNC_SCALAR)
{
/* Base data type, i.e. scalar */
! buildScalarFunctionAlias(linitial(funcexprs),
! strVal(linitial(funcnames)), rangefunc->is_table,
! alias, eref, rangefunc->ordinality);
}
else
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("function \"%s\" in FROM has unsupported return type %s",
! strVal(linitial(funcnames)), format_type_be(funcrettype)),
! parser_errposition(pstate, exprLocation(linitial(funcexprs)))));
/*
* Set flags and access permissions.
*************** expandRTE(RangeTblEntry *rte, int rtinde
*** 1783,1886 ****
TypeFuncClass functypclass;
Oid funcrettype;
TupleDesc tupdesc;
! int ordinality_attno = 0;
!
! functypclass = get_expr_result_type(rte->funcexpr,
! &funcrettype,
! &tupdesc);
! if (functypclass == TYPEFUNC_COMPOSITE)
! {
! /* Composite data type, e.g. a table's row type */
! Assert(tupdesc);
!
! /*
! * we rely here on the fact that expandTupleDesc doesn't
! * care about being passed more aliases than it needs.
! */
! expandTupleDesc(tupdesc, rte->eref,
! rtindex, sublevels_up, location,
! include_dropped, colnames, colvars);
! ordinality_attno = tupdesc->natts + 1;
! }
! else if (functypclass == TYPEFUNC_SCALAR)
{
! /* Base data type, i.e. scalar */
! if (colnames)
! *colnames = lappend(*colnames,
! linitial(rte->eref->colnames));
!
! if (colvars)
{
! Var *varnode;
! varnode = makeVar(rtindex, 1,
! funcrettype, -1,
! exprCollation(rte->funcexpr),
! sublevels_up);
! varnode->location = location;
! *colvars = lappend(*colvars, varnode);
}
!
! ordinality_attno = 2;
! }
! else if (functypclass == TYPEFUNC_RECORD)
! {
! if (colnames)
! *colnames = copyObject(rte->eref->colnames);
! if (colvars)
{
! ListCell *l1;
! ListCell *l2;
! ListCell *l3;
! int attnum = 0;
! forthree(l1, rte->funccoltypes,
! l2, rte->funccoltypmods,
! l3, rte->funccolcollations)
{
- Oid attrtype = lfirst_oid(l1);
- int32 attrtypmod = lfirst_int(l2);
- Oid attrcollation = lfirst_oid(l3);
Var *varnode;
! attnum++;
! varnode = makeVar(rtindex,
! attnum,
! attrtype,
! attrtypmod,
! attrcollation,
sublevels_up);
varnode->location = location;
*colvars = lappend(*colvars, varnode);
}
- }
! /* note, ordinality is not allowed in this case */
! }
! else
! {
! /* addRangeTableEntryForFunction should've caught this */
! elog(ERROR, "function in FROM has unsupported return type");
}
/* tack on the extra ordinality column if present */
if (rte->funcordinality)
{
! Assert(ordinality_attno > 0);
if (colnames)
*colnames = lappend(*colnames, llast(rte->eref->colnames));
if (colvars)
{
! Var *varnode = makeVar(rtindex,
! ordinality_attno,
! INT8OID,
! -1,
! InvalidOid,
! sublevels_up);
*colvars = lappend(*colvars, varnode);
}
}
--- 1874,1956 ----
TypeFuncClass functypclass;
Oid funcrettype;
TupleDesc tupdesc;
! int atts_done = 0;
! ListCell *lc;
! /*
! * Loop over functions to assemble result.
! *
! * atts_done is the number of attributes (including dropped
! * cols) constructed so far; it's used as an index offset in
! * various places.
! */
! foreach(lc, rte->funcexprs)
{
! functypclass = get_expr_result_type(lfirst(lc),
! &funcrettype,
! &tupdesc);
! if (functypclass == TYPEFUNC_COMPOSITE)
{
! /* Composite data type, e.g. a table's row type */
! Assert(tupdesc);
! /*
! * we rely here on the fact that expandTupleDesc
! * doesn't care about being passed more aliases than
! * it needs.
! */
! expandTupleDesc(tupdesc, rte->eref, atts_done,
! rtindex, sublevels_up, location,
! include_dropped, colnames, colvars);
! atts_done += tupdesc->natts;
}
! else if (functypclass == TYPEFUNC_SCALAR)
{
! /* Base data type, i.e. scalar */
! if (colnames)
! *colnames = lappend(*colnames,
! list_nth(rte->eref->colnames, atts_done));
! if (colvars)
{
Var *varnode;
! varnode = makeVar(rtindex, atts_done + 1,
! funcrettype, -1,
! exprCollation(lfirst(lc)),
sublevels_up);
varnode->location = location;
+
*colvars = lappend(*colvars, varnode);
}
! ++atts_done;
! }
! else
! {
! /* addRangeTableEntryForFunction should've caught this */
! elog(ERROR, "function in FROM has unsupported return type");
! }
}
/* tack on the extra ordinality column if present */
if (rte->funcordinality)
{
! Assert(atts_done > 0);
if (colnames)
*colnames = lappend(*colnames, llast(rte->eref->colnames));
if (colvars)
{
! Var *varnode = makeVar(rtindex,
! atts_done + 1,
! INT8OID,
! -1,
! InvalidOid,
! sublevels_up);
!
*colvars = lappend(*colvars, varnode);
}
}
*************** expandRelation(Oid relid, Alias *eref, i
*** 2051,2057 ****
/* Get the tupledesc and turn it over to expandTupleDesc */
rel = relation_open(relid, AccessShareLock);
! expandTupleDesc(rel->rd_att, eref, rtindex, sublevels_up,
location, include_dropped,
colnames, colvars);
relation_close(rel, AccessShareLock);
--- 2121,2127 ----
/* Get the tupledesc and turn it over to expandTupleDesc */
rel = relation_open(relid, AccessShareLock);
! expandTupleDesc(rel->rd_att, eref, 0, rtindex, sublevels_up,
location, include_dropped,
colnames, colvars);
relation_close(rel, AccessShareLock);
*************** expandRelation(Oid relid, Alias *eref, i
*** 2060,2076 ****
/*
* expandTupleDesc -- expandRTE subroutine
*
! * Only the required number of column names are used from the Alias;
! * it is not an error to supply too many. (ordinality depends on this)
*/
static void
! expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars)
{
int maxattrs = tupdesc->natts;
! int numaliases = list_length(eref->colnames);
int varattno;
for (varattno = 0; varattno < maxattrs; varattno++)
--- 2130,2149 ----
/*
* expandTupleDesc -- expandRTE subroutine
*
! * Only the required number of column names are used from the Alias; it is not
! * an error to supply too many. (ordinality depends on this)
! *
! * atts_done offsets the resulting column numbers, for the function case when
! * we merge multiple tupdescs into one list.
*/
static void
! expandTupleDesc(TupleDesc tupdesc, Alias *eref, int atts_done,
int rtindex, int sublevels_up,
int location, bool include_dropped,
List **colnames, List **colvars)
{
int maxattrs = tupdesc->natts;
! int numaliases = list_length(eref->colnames) - atts_done;
int varattno;
for (varattno = 0; varattno < maxattrs; varattno++)
*************** expandTupleDesc(TupleDesc tupdesc, Alias
*** 2101,2107 ****
char *label;
if (varattno < numaliases)
! label = strVal(list_nth(eref->colnames, varattno));
else
label = NameStr(attr->attname);
*colnames = lappend(*colnames, makeString(pstrdup(label)));
--- 2174,2180 ----
char *label;
if (varattno < numaliases)
! label = strVal(list_nth(eref->colnames, varattno + atts_done));
else
label = NameStr(attr->attname);
*colnames = lappend(*colnames, makeString(pstrdup(label)));
*************** expandTupleDesc(TupleDesc tupdesc, Alias
*** 2111,2117 ****
{
Var *varnode;
! varnode = makeVar(rtindex, attr->attnum,
attr->atttypid, attr->atttypmod,
attr->attcollation,
sublevels_up);
--- 2184,2190 ----
{
Var *varnode;
! varnode = makeVar(rtindex, attr->attnum + atts_done,
attr->atttypid, attr->atttypmod,
attr->attcollation,
sublevels_up);
*************** get_rte_attribute_type(RangeTblEntry *rt
*** 2281,2291 ****
TypeFuncClass functypclass;
Oid funcrettype;
TupleDesc tupdesc;
/*
! * if ordinality, then a reference to the last column
! * in the name list must be referring to the
! * ordinality column
*/
if (rte->funcordinality
&& attnum == list_length(rte->eref->colnames))
--- 2354,2365 ----
TypeFuncClass functypclass;
Oid funcrettype;
TupleDesc tupdesc;
+ ListCell *lc;
+ int atts_done = 0;
/*
! * if ordinality, then a reference to the last column in the
! * name list must be referring to the ordinality column
*/
if (rte->funcordinality
&& attnum == list_length(rte->eref->colnames))
*************** get_rte_attribute_type(RangeTblEntry *rt
*** 2296,2356 ****
break;
}
! functypclass = get_expr_result_type(rte->funcexpr,
! &funcrettype,
! &tupdesc);
!
! if (functypclass == TYPEFUNC_COMPOSITE)
{
! /* Composite data type, e.g. a table's row type */
! Form_pg_attribute att_tup;
! Assert(tupdesc);
! /* this is probably a can't-happen case */
! if (attnum < 1 || attnum > tupdesc->natts)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("column %d of relation \"%s\" does not exist",
! attnum,
! rte->eref->aliasname)));
! att_tup = tupdesc->attrs[attnum - 1];
! /*
! * If dropped column, pretend it ain't there. See notes
! * in scanRTEForColumn.
! */
! if (att_tup->attisdropped)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("column \"%s\" of relation \"%s\" does not exist",
! NameStr(att_tup->attname),
! rte->eref->aliasname)));
! *vartype = att_tup->atttypid;
! *vartypmod = att_tup->atttypmod;
! *varcollid = att_tup->attcollation;
! }
! else if (functypclass == TYPEFUNC_SCALAR)
! {
! Assert(attnum == 1);
! /* Base data type, i.e. scalar */
! *vartype = funcrettype;
! *vartypmod = -1;
! *varcollid = exprCollation(rte->funcexpr);
! }
! else if (functypclass == TYPEFUNC_RECORD)
! {
! *vartype = list_nth_oid(rte->funccoltypes, attnum - 1);
! *vartypmod = list_nth_int(rte->funccoltypmods, attnum - 1);
! *varcollid = list_nth_oid(rte->funccolcollations, attnum - 1);
! }
! else
! {
! /* addRangeTableEntryForFunction should've caught this */
! elog(ERROR, "function in FROM has unsupported return type");
}
}
break;
case RTE_VALUES:
--- 2370,2441 ----
break;
}
! /*
! * Loop over funcs until we find the one that covers the
! * requested column.
! */
! foreach(lc, rte->funcexprs)
{
! functypclass = get_expr_result_type(lfirst(lc),
! &funcrettype,
! &tupdesc);
! if (functypclass == TYPEFUNC_COMPOSITE)
! {
! /* Composite data type, e.g. a table's row type */
! Form_pg_attribute att_tup;
! Assert(tupdesc);
! if (attnum > atts_done
! && attnum <= atts_done + tupdesc->natts)
! {
! att_tup = tupdesc->attrs[attnum - atts_done - 1];
! /*
! * If dropped column, pretend it ain't there. See
! * notes in scanRTEForColumn.
! */
! if (att_tup->attisdropped)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_COLUMN),
! errmsg("column \"%s\" of relation \"%s\" does not exist",
! NameStr(att_tup->attname),
! rte->eref->aliasname)));
! *vartype = att_tup->atttypid;
! *vartypmod = att_tup->atttypmod;
! *varcollid = att_tup->attcollation;
! return;
! }
! atts_done += tupdesc->natts;
! }
! else if (functypclass == TYPEFUNC_SCALAR)
! {
! if (attnum == atts_done + 1)
! {
! /* Base data type, i.e. scalar */
! *vartype = funcrettype;
! *vartypmod = -1;
! *varcollid = exprCollation(lfirst(lc));
! return;
! }
!
! ++atts_done;
! }
! else
! {
! /* addRangeTableEntryForFunction should've caught this */
! elog(ERROR, "function in FROM has unsupported return type");
! }
}
+
+ /* this is probably a can't-happen case */
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column %d of relation \"%s\" does not exist",
+ attnum,
+ rte->eref->aliasname)));
}
break;
case RTE_VALUES:
*************** get_rte_attribute_is_dropped(RangeTblEnt
*** 2456,2500 ****
case RTE_FUNCTION:
{
/* Function RTE */
! Oid funcrettype = exprType(rte->funcexpr);
! Oid funcrelid = typeidTypeRelid(funcrettype);
/*
! * if ordinality, then a reference to the last column
! * in the name list must be referring to the
! * ordinality column, which is not dropped
*/
if (rte->funcordinality
&& attnum == list_length(rte->eref->colnames))
! {
! result = false;
! }
! else if (OidIsValid(funcrelid))
! {
! /*
! * Composite data type, i.e. a table's row type
! *
! * Same as ordinary relation RTE
! */
! HeapTuple tp;
! Form_pg_attribute att_tup;
! tp = SearchSysCache2(ATTNUM,
! ObjectIdGetDatum(funcrelid),
! Int16GetDatum(attnum));
! if (!HeapTupleIsValid(tp)) /* shouldn't happen */
! elog(ERROR, "cache lookup failed for attribute %d of relation %u",
! attnum, funcrelid);
! att_tup = (Form_pg_attribute) GETSTRUCT(tp);
! result = att_tup->attisdropped;
! ReleaseSysCache(tp);
! }
! else
{
! /*
! * Must be a base data type, i.e. scalar
! */
! result = false;
}
}
break;
--- 2541,2607 ----
case RTE_FUNCTION:
{
/* Function RTE */
! TypeFuncClass functypclass;
! Oid funcrettype;
! TupleDesc tupdesc;
! ListCell *lc;
! int atts_done = 0;
!
! result = false;
/*
! * if ordinality, then a reference to the last column in the
! * name list must be referring to the ordinality column, which
! * is not dropped
*/
if (rte->funcordinality
&& attnum == list_length(rte->eref->colnames))
! break;
! /*
! * Loop over funcs until we find the one that covers the
! * requested column.
! */
! foreach(lc, rte->funcexprs)
{
! functypclass = get_expr_result_type(lfirst(lc),
! &funcrettype,
! &tupdesc);
!
! if (functypclass == TYPEFUNC_COMPOSITE)
! {
! /* Composite data type, e.g. a table's row type */
! Form_pg_attribute att_tup;
!
! Assert(tupdesc);
!
! if (attnum > atts_done
! && attnum <= atts_done + tupdesc->natts)
! {
! att_tup = tupdesc->attrs[attnum - atts_done - 1];
!
! result = att_tup->attisdropped;
! break;
! }
!
! atts_done += tupdesc->natts;
! }
! else if (functypclass == TYPEFUNC_SCALAR)
! {
! if (attnum == atts_done + 1)
! {
! /* Base data type, i.e. scalar */
! result = false;
! break;
! }
!
! ++atts_done;
! }
! else
! {
! /* addRangeTableEntryForFunction should've caught this */
! elog(ERROR, "function in FROM has unsupported return type");
! }
}
}
break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index c52a374..94f3cf5 100644
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
*************** rewriteRuleAction(Query *parsetree,
*** 390,396 ****
{
case RTE_FUNCTION:
sub_action->hasSubLinks =
! checkExprHasSubLink(rte->funcexpr);
break;
case RTE_VALUES:
sub_action->hasSubLinks =
--- 390,396 ----
{
case RTE_FUNCTION:
sub_action->hasSubLinks =
! checkExprHasSubLink((Node *) rte->funcexprs);
break;
case RTE_VALUES:
sub_action->hasSubLinks =
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 5ffce68..6fe0075 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
+ #include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
*************** static void get_from_clause_item(Node *j
*** 387,393 ****
deparse_context *context);
static void get_column_alias_list(deparse_columns *colinfo,
deparse_context *context);
! static void get_from_clause_coldeflist(deparse_columns *colinfo,
List *types, List *typmods, List *collations,
deparse_context *context);
static void get_opclass_name(Oid opclass, Oid actual_datatype,
--- 388,394 ----
deparse_context *context);
static void get_column_alias_list(deparse_columns *colinfo,
deparse_context *context);
! static void get_from_clause_coldeflist(deparse_columns *colinfo, List *names,
List *types, List *typmods, List *collations,
deparse_context *context);
static void get_opclass_name(Oid opclass, Oid actual_datatype,
*************** get_from_clause_item(Node *jtnode, Query
*** 8013,8018 ****
--- 8014,8020 ----
char *refname = get_rtable_name(varno, context);
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
bool printalias;
+ FuncExpr *func_coldef = NULL;
if (rte->lateral)
appendStringInfoString(buf, "LATERAL ");
*************** get_from_clause_item(Node *jtnode, Query
*** 8037,8043 ****
break;
case RTE_FUNCTION:
/* Function RTE */
! get_rule_expr(rte->funcexpr, context, true);
if (rte->funcordinality)
appendStringInfoString(buf, " WITH ORDINALITY");
break;
--- 8039,8139 ----
break;
case RTE_FUNCTION:
/* Function RTE */
!
! /*
! * The simple case of omitting TABLE() for one function only
! * works if either there's no ordinality, or the function does
! * not need a column definition list.
! */
! if (list_length(rte->funcexprs) == 1
! && (!rte->funcordinality
! || !IsA(linitial(rte->funcexprs), FuncExpr)
! ||((FuncExpr *) linitial(rte->funcexprs))->funccoltypes == NIL))
! {
! get_rule_expr(linitial(rte->funcexprs), context, true);
! func_coldef = linitial(rte->funcexprs);
! }
! else
! {
! ListCell *lc = list_head(rte->funcexprs);
! Oid unnest_oid = InvalidOid;
!
! /*
! * If all the function calls in the list are to
! * pg_catalog.unnest, then collapse the list back down to
! * UNNEST(args). Since there's currently only one unnest,
! * we check by oid after the first one.
! */
!
! if (IsA(lfirst(lc), FuncExpr))
! {
! unnest_oid = ((FuncExpr *) lfirst(lc))->funcid;
!
! if (get_func_namespace(unnest_oid) != PG_CATALOG_NAMESPACE
! || strcmp(get_func_name(unnest_oid), "unnest") != 0)
! unnest_oid = InvalidOid;
! }
!
! while (OidIsValid(unnest_oid))
! {
! FuncExpr *fn;
!
! lc = lnext(lc);
! if (!lc)
! break;
!
! fn = lfirst(lc);
! if (!IsA(fn, FuncExpr)
! ||fn->funcid != unnest_oid
! || fn->funccoltypes != NIL)
! unnest_oid = InvalidOid;
! }
!
! if (OidIsValid(unnest_oid))
! {
! List *allargs = NIL;
!
! foreach(lc, rte->funcexprs)
! {
! List *args = ((FuncExpr *) lfirst(lc))->args;
!
! allargs = list_concat(allargs, list_copy(args));
! }
!
! appendStringInfoString(buf, "unnest(");
! get_rule_expr((Node *) allargs, context, true);
! }
! else
! {
! appendStringInfoString(buf, "TABLE(");
!
! foreach(lc, rte->funcexprs)
! {
! FuncExpr *fn = lfirst(lc);
!
! get_rule_expr((Node *) fn, context, true);
!
! if (IsA(fn, FuncExpr) &&fn->funccoltypes != NIL)
! {
! /*
! * Function returning RECORD, reconstruct the
! * columndefs
! */
! appendStringInfoString(buf, " AS ");
! get_from_clause_coldeflist(NULL,
! fn->funccolnames,
! fn->funccoltypes,
! fn->funccoltypmods,
! fn->funccolcollations,
! context);
! }
!
! if (lnext(lc))
! appendStringInfoString(buf, ", ");
! }
! }
! appendStringInfoChar(buf, ')');
! }
if (rte->funcordinality)
appendStringInfoString(buf, " WITH ORDINALITY");
break;
*************** get_from_clause_item(Node *jtnode, Query
*** 8099,8111 ****
appendStringInfo(buf, " %s", quote_identifier(refname));
/* Print the column definitions or aliases, if needed */
! if (rte->rtekind == RTE_FUNCTION && rte->funccoltypes != NIL)
{
/* Function returning RECORD, reconstruct the columndefs */
get_from_clause_coldeflist(colinfo,
! rte->funccoltypes,
! rte->funccoltypmods,
! rte->funccolcollations,
context);
}
else
--- 8195,8208 ----
appendStringInfo(buf, " %s", quote_identifier(refname));
/* Print the column definitions or aliases, if needed */
! if (rte->rtekind == RTE_FUNCTION && func_coldef && func_coldef->funccoltypes != NIL)
{
/* Function returning RECORD, reconstruct the columndefs */
get_from_clause_coldeflist(colinfo,
! NIL,
! func_coldef->funccoltypes,
! func_coldef->funccoltypmods,
! func_coldef->funccolcollations,
context);
}
else
*************** get_column_alias_list(deparse_columns *c
*** 8254,8260 ****
* responsible for ensuring that an alias or AS is present before it.
*/
static void
! get_from_clause_coldeflist(deparse_columns *colinfo,
List *types, List *typmods, List *collations,
deparse_context *context)
{
--- 8351,8357 ----
* responsible for ensuring that an alias or AS is present before it.
*/
static void
! get_from_clause_coldeflist(deparse_columns *colinfo, List *names,
List *types, List *typmods, List *collations,
deparse_context *context)
{
*************** get_from_clause_coldeflist(deparse_colum
*** 8262,8267 ****
--- 8359,8365 ----
ListCell *l1;
ListCell *l2;
ListCell *l3;
+ ListCell *l4 = (names ? list_head(names) : NULL);
int i;
appendStringInfoChar(buf, '(');
*************** get_from_clause_coldeflist(deparse_colum
*** 8269,8275 ****
i = 0;
forthree(l1, types, l2, typmods, l3, collations)
{
! char *attname = colinfo->colnames[i];
Oid atttypid = lfirst_oid(l1);
int32 atttypmod = lfirst_int(l2);
Oid attcollation = lfirst_oid(l3);
--- 8367,8373 ----
i = 0;
forthree(l1, types, l2, typmods, l3, collations)
{
! char *attname = (colinfo ? colinfo->colnames[i] : strVal(lfirst(l4)));
Oid atttypid = lfirst_oid(l1);
int32 atttypmod = lfirst_int(l2);
Oid attcollation = lfirst_oid(l3);
*************** get_from_clause_coldeflist(deparse_colum
*** 8285,8290 ****
--- 8383,8391 ----
attcollation != get_typcollation(atttypid))
appendStringInfo(buf, " COLLATE %s",
generate_collation_name(attcollation));
+
+ if (!colinfo)
+ l4 = lnext(l4);
i++;
}
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
index 6347a8f..73f9f04 100644
*** a/src/backend/utils/fmgr/funcapi.c
--- b/src/backend/utils/fmgr/funcapi.c
*************** internal_get_result_type(Oid funcid,
*** 375,381 ****
case TYPEFUNC_SCALAR:
break;
case TYPEFUNC_RECORD:
! /* We must get the tupledesc from call context */
if (rsinfo && IsA(rsinfo, ReturnSetInfo) &&
rsinfo->expectedDesc != NULL)
{
--- 375,386 ----
case TYPEFUNC_SCALAR:
break;
case TYPEFUNC_RECORD:
!
! /*
! * We prefer to get the tupledesc from the call context since that
! * is already built. If there isn't one, we try and cons it up
! * from the funccol* fields of FuncExpr.
! */
if (rsinfo && IsA(rsinfo, ReturnSetInfo) &&
rsinfo->expectedDesc != NULL)
{
*************** internal_get_result_type(Oid funcid,
*** 384,389 ****
--- 389,410 ----
*resultTupleDesc = rsinfo->expectedDesc;
/* Assume no polymorphic columns here, either */
}
+ else if (call_expr && IsA(call_expr, FuncExpr))
+ {
+ FuncExpr *func = (FuncExpr *) call_expr;
+
+ if (func->funccoltypes != NIL)
+ {
+ tupdesc = BuildDescFromLists(func->funccolnames,
+ func->funccoltypes,
+ func->funccoltypmods,
+ func->funccolcollations);
+ result = TYPEFUNC_COMPOSITE;
+ if (resultTupleDesc)
+ *resultTupleDesc = tupdesc;
+ /* Assume no polymorphic columns here, either */
+ }
+ }
break;
default:
break;
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 49226b7..6bf3b35 100644
*** a/src/include/access/tupdesc.h
--- b/src/include/access/tupdesc.h
*************** extern TupleDesc CreateTupleDesc(int nat
*** 87,93 ****
Form_pg_attribute *attrs);
extern TupleDesc CreateTupleDescCopy(TupleDesc tupdesc);
- extern TupleDesc CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts);
extern TupleDesc CreateTupleDescCopyConstr(TupleDesc tupdesc);
--- 87,92 ----
*************** extern void TupleDescInitEntryCollation(
*** 121,126 ****
--- 120,128 ----
AttrNumber attributeNumber,
Oid collationid);
+ extern void TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
+ const TupleDesc src, AttrNumber srcAttno);
+
extern TupleDesc BuildDescForRelation(List *schema);
extern TupleDesc BuildDescFromLists(List *names, List *types, List *typmods, List *collations);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index bedcf04..dcc0a5e 100644
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
*************** typedef struct SubqueryScanState
*** 1395,1418 ****
* function appearing in FROM (typically a function returning set).
*
* eflags node's capability flags
! * ordinal column value for WITH ORDINALITY
* scan_tupdesc scan tuple descriptor
! * func_tupdesc function tuple descriptor
! * func_slot function result slot, or null
! * tuplestorestate private state of tuplestore.c
! * funcexpr state for function expression being evaluated
* ----------------
*/
typedef struct FunctionScanState
{
ScanState ss; /* its first field is NodeTag */
int eflags;
! int64 ordinal;
TupleDesc scan_tupdesc;
! TupleDesc func_tupdesc;
! TupleTableSlot *func_slot;
! Tuplestorestate *tuplestorestate;
! ExprState *funcexpr;
} FunctionScanState;
/* ----------------
--- 1395,1421 ----
* function appearing in FROM (typically a function returning set).
*
* eflags node's capability flags
! * ordinality is this scan WITH ORDINALITY?
! * ordinal current ordinal column value
* scan_tupdesc scan tuple descriptor
! * nfuncs number of functions being executed
! * funcstates per-function execution states (private in
! * nodeFunctionscan.c)
* ----------------
*/
+ struct FunctionScanPerFuncState;
+
typedef struct FunctionScanState
{
ScanState ss; /* its first field is NodeTag */
int eflags;
! bool ordinality;
! bool simple;
! int64 ordinal;
TupleDesc scan_tupdesc;
! int nfuncs;
! struct FunctionScanPerFuncState *funcstates; /* array of length
! * nfuncs */
} FunctionScanState;
/* ----------------
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 55524b4..e881795 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct FuncCall
*** 304,309 ****
--- 304,310 ----
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
struct WindowDef *over; /* OVER clause, if any */
+ List *coldeflist; /* column definition list for record funcs */
int location; /* token location, or -1 if unknown */
} FuncCall;
*************** typedef struct RangeSubselect
*** 466,481 ****
/*
* RangeFunction - function call appearing in a FROM clause
*/
typedef struct RangeFunction
{
NodeTag type;
bool lateral; /* does it have LATERAL prefix? */
bool ordinality; /* does it have WITH ORDINALITY suffix? */
! Node *funccallnode; /* untransformed function call tree */
Alias *alias; /* table alias & optional column aliases */
- List *coldeflist; /* list of ColumnDef nodes to describe result
- * of function returning RECORD */
} RangeFunction;
/*
--- 467,484 ----
/*
* RangeFunction - function call appearing in a FROM clause
+ *
+ * funccallnodes is a list because we use this to represent the construct
+ * TABLE(func1(...),func2(...),...) AS ...
*/
typedef struct RangeFunction
{
NodeTag type;
bool lateral; /* does it have LATERAL prefix? */
bool ordinality; /* does it have WITH ORDINALITY suffix? */
! bool is_table; /* result of TABLE() syntax */
! List *funccallnodes; /* untransformed function call trees */
Alias *alias; /* table alias & optional column aliases */
} RangeFunction;
/*
*************** typedef struct XmlSerialize
*** 653,664 ****
* colnames for columns dropped since the rule was created (and for that
* matter the colnames might be out of date due to column renamings).
*
! * The same comments apply to FUNCTION RTEs when the function's return type
* is a named composite type. In addition, for all return types, FUNCTION
! * RTEs with ORDINALITY must always have the last colname entry being the
! * one for the ordinal column; this is enforced when constructing the RTE.
! * Thus when ORDINALITY is used, there will be exactly one more colname
! * than would have been present otherwise.
*
* In JOIN RTEs, the colnames in both alias and eref are one-to-one with
* joinaliasvars entries. A JOIN RTE will omit columns of its inputs when
--- 656,667 ----
* colnames for columns dropped since the rule was created (and for that
* matter the colnames might be out of date due to column renamings).
*
! * The same comments apply to FUNCTION RTEs when a function's return type
* is a named composite type. In addition, for all return types, FUNCTION
! * RTEs with ORDINALITY must always have the last colname entry being the
! * one for the ordinal column; this is enforced when constructing the RTE.
! * Thus when ORDINALITY is used, there will be exactly one more colname
! * than would have been present otherwise.
*
* In JOIN RTEs, the colnames in both alias and eref are one-to-one with
* joinaliasvars entries. A JOIN RTE will omit columns of its inputs when
*************** typedef struct RangeTblEntry
*** 757,777 ****
/*
* Fields valid for a function RTE (else NULL):
*
! * If the function returns an otherwise-unspecified RECORD, funccoltypes
! * lists the column types declared in the RTE's column type specification,
! * funccoltypmods lists their declared typmods, funccolcollations their
! * collations. Note that in this case, ORDINALITY is not permitted, so
! * there is no extra ordinal column to be allowed for.
*
! * Otherwise, those fields are NIL, and the result column types must be
! * derived from the funcexpr while treating the ordinal column, if
! * present, as a special case. (see get_rte_attribute_*)
*/
! Node *funcexpr; /* expression tree for func call */
! List *funccoltypes; /* OID list of column type OIDs */
! List *funccoltypmods; /* integer list of column typmods */
! List *funccolcollations; /* OID list of column collation OIDs */
! bool funcordinality; /* is this called WITH ORDINALITY? */
/*
* Fields valid for a values RTE (else NIL):
--- 760,775 ----
/*
* Fields valid for a function RTE (else NULL):
*
! * If the function returns an otherwise-unspecified RECORD, we used to
! * store type lists here; we now push those down to the individual
! * FuncExpr nodes, so that we can handle multiple RECORD functions and/or
! * RECORD functions with ordinality.
*
! * So, in all cases the result column types can be determined from the
! * funcexprs, with the ordinality column, if present, appended to the end.
*/
! List *funcexprs; /* expression trees for func calls */
! bool funcordinality; /* is this called WITH ORDINALITY? */
/*
* Fields valid for a values RTE (else NIL):
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 44ea0b7..b5df47c 100644
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
*************** typedef struct SubqueryScan
*** 424,435 ****
typedef struct FunctionScan
{
Scan scan;
! Node *funcexpr; /* expression tree for func call */
! bool funcordinality; /* WITH ORDINALITY */
! List *funccolnames; /* output column names (string Value nodes) */
! List *funccoltypes; /* OID list of column type OIDs */
! List *funccoltypmods; /* integer list of column typmods */
! List *funccolcollations; /* OID list of column collation OIDs */
} FunctionScan;
/* ----------------
--- 424,434 ----
typedef struct FunctionScan
{
Scan scan;
! List *funcexprs; /* expression trees for func calls */
! List *funccolnames; /* result column names */
! bool funcordinality; /* WITH ORDINALITY */
! /* keep this last due to nonstandard output */
! List *funcparams; /* Bitmapsets for params used by each func */
} FunctionScan;
/* ----------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 7918537..ba97f00 100644
*** a/src/include/nodes/primnodes.h
--- b/src/include/nodes/primnodes.h
*************** typedef struct FuncExpr
*** 354,359 ****
--- 354,365 ----
Oid funccollid; /* OID of collation of result */
Oid inputcollid; /* OID of collation that function should use */
List *args; /* arguments to the function */
+ /* These func* fields are used only for table functions returning RECORD */
+ List *funccolnames; /* result colnames for RECORD rangefuncs */
+ List *funccoltypes; /* result coltypes for RECORD rangefuncs */
+ List *funccoltypmods; /* result coltypmods for RECORD rangefuncs */
+ List *funccolcollations; /* result colcollations for RECORD
+ * rangefuncs */
int location; /* token location, or -1 if unknown */
} FuncExpr;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 9686229..0033a3c 100644
*** a/src/include/optimizer/pathnode.h
--- b/src/include/optimizer/pathnode.h
*************** extern UniquePath *create_unique_path(Pl
*** 70,76 ****
extern Path *create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel,
List *pathkeys, Relids required_outer);
extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel,
! Relids required_outer);
extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
--- 70,76 ----
extern Path *create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel,
List *pathkeys, Relids required_outer);
extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel,
! List *pathkeys, Relids required_outer);
extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel,
Relids required_outer);
extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 96ffdb1..faccbda 100644
*** a/src/include/optimizer/paths.h
--- b/src/include/optimizer/paths.h
*************** extern Path *get_cheapest_fractional_pat
*** 166,171 ****
--- 166,173 ----
double fraction);
extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
ScanDirection scandir);
+ extern List *build_expression_pathkey(PlannerInfo *root, RelOptInfo *rel,
+ Expr *expr, Oid opno, bool nulls_first);
extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
List *subquery_pathkeys);
extern List *build_join_pathkeys(PlannerInfo *root,
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index d33eef3..867d1ff 100644
*** a/src/include/parser/parse_func.h
--- b/src/include/parser/parse_func.h
*************** typedef enum
*** 43,51 ****
extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
! List *agg_order, Expr *agg_filter,
! bool agg_star, bool agg_distinct, bool func_variadic,
! WindowDef *over, bool is_column, int location);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
--- 43,49 ----
extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
! int location, FuncCall *fn);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 83c9131..808d341 100644
*** a/src/include/parser/parse_relation.h
--- b/src/include/parser/parse_relation.h
*************** extern RangeTblEntry *addRangeTableEntry
*** 58,65 ****
bool lateral,
bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForFunction(ParseState *pstate,
! char *funcname,
! Node *funcexpr,
RangeFunction *rangefunc,
bool lateral,
bool inFromCl);
--- 58,65 ----
bool lateral,
bool inFromCl);
extern RangeTblEntry *addRangeTableEntryForFunction(ParseState *pstate,
! List *funcnames,
! List *funcexprs,
RangeFunction *rangefunc,
bool lateral,
bool inFromCl);
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 37391dc..fe6550e 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** select definition from pg_views where vi
*** 87,145 ****
(1 row)
drop view vw_ord;
! -- ordinality vs. rewind and reverse scan
begin;
! declare foo scroll cursor for select * from generate_series(1,5) with ordinality as g(i,o);
fetch all from foo;
! i | o
! ---+---
! 1 | 1
! 2 | 2
! 3 | 3
! 4 | 4
! 5 | 5
(5 rows)
fetch backward all from foo;
! i | o
! ---+---
! 5 | 5
! 4 | 4
! 3 | 3
! 2 | 2
! 1 | 1
(5 rows)
fetch all from foo;
! i | o
! ---+---
! 1 | 1
! 2 | 2
! 3 | 3
! 4 | 4
! 5 | 5
(5 rows)
fetch next from foo;
! i | o
! ---+---
(0 rows)
fetch next from foo;
! i | o
! ---+---
(0 rows)
fetch prior from foo;
! i | o
! ---+---
! 5 | 5
(1 row)
fetch absolute 1 from foo;
! i | o
! ---+---
! 1 | 1
(1 row)
commit;
--- 87,293 ----
(1 row)
drop view vw_ord;
! -- multiple functions
! select * from table(foot(1),foot(2)) with ordinality as z(a,b,c,d,ord);
! a | b | c | d | ord
! ---+-----+---+----+-----
! 1 | 11 | 2 | 22 | 1
! 1 | 111 | | | 2
! (2 rows)
!
! create temporary view vw_ord as select * from (values (1)) v(n) join table(foot(1),foot(2)) with ordinality as
z(a,b,c,d,ord)on (n=ord);
! select * from vw_ord;
! n | a | b | c | d | ord
! ---+---+----+---+----+-----
! 1 | 1 | 11 | 2 | 22 | 1
! (1 row)
!
! select definition from pg_views where viewname='vw_ord';
! definition
! -----------------------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.c, +
! z.d, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN TABLE(foot(1), foot(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
! (1 row)
!
! drop view vw_ord;
! -- expansions of unnest()
! select * from unnest(array[10,20],array['foo','bar'],array[1.0]);
! unnest | unnest | unnest
! --------+--------+--------
! 10 | foo | 1.0
! 20 | bar |
! (2 rows)
!
! select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord);
! a | b | c | ord
! ----+-----+-----+-----
! 10 | foo | 1.0 | 1
! 20 | bar | | 2
! (2 rows)
!
! select * from table(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord);
! a | b | c | ord
! ----+-----+-----+-----
! 10 | foo | 1.0 | 1
! 20 | bar | | 2
! (2 rows)
!
! select * from table(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as
z(a,b,c,ord);
! a | b | c | ord
! ----+-----+-----+-----
! 10 | foo | 101 | 1
! 20 | bar | 102 | 2
! (2 rows)
!
! create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c);
! select * from vw_ord;
! a | b | c
! ----+-----+-----
! 10 | foo | 1.0
! 20 | bar |
! (2 rows)
!
! select definition from pg_views where viewname='vw_ord';
! definition
! ----------------------------------------------------------------------------------------
! SELECT z.a, +
! z.b, +
! z.c +
! FROM unnest(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
! (1 row)
!
! drop view vw_ord;
! create temporary view vw_ord as select * from table(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c);
! select * from vw_ord;
! a | b | c
! ----+-----+-----
! 10 | foo | 1.0
! 20 | bar |
! (2 rows)
!
! select definition from pg_views where viewname='vw_ord';
! definition
! ----------------------------------------------------------------------------------------
! SELECT z.a, +
! z.b, +
! z.c +
! FROM unnest(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
! (1 row)
!
! drop view vw_ord;
! create temporary view vw_ord as select * from table(unnest(array[10,20],array['foo','bar']), generate_series(1,2)) as
z(a,b,c);
! select * from vw_ord;
! a | b | c
! ----+-----+---
! 10 | foo | 1
! 20 | bar | 2
! (2 rows)
!
! select definition from pg_views where viewname='vw_ord';
! definition
! ------------------------------------------------------------------------------------------------------------------
! SELECT z.a, +
! z.b, +
! z.c +
! FROM TABLE(unnest(ARRAY[10, 20]), unnest(ARRAY['foo'::text, 'bar'::text]), generate_series(1, 2)) z(a, b, c);
! (1 row)
!
! drop view vw_ord;
! -- ordinality and multiple functions vs. rewind and reverse scan
begin;
! declare foo scroll cursor for select * from table(generate_series(1,5),generate_series(1,2)) with ordinality as
g(i,j,o);
fetch all from foo;
! i | j | o
! ---+---+---
! 1 | 1 | 1
! 2 | 2 | 2
! 3 | | 3
! 4 | | 4
! 5 | | 5
(5 rows)
fetch backward all from foo;
! i | j | o
! ---+---+---
! 5 | | 5
! 4 | | 4
! 3 | | 3
! 2 | 2 | 2
! 1 | 1 | 1
(5 rows)
fetch all from foo;
! i | j | o
! ---+---+---
! 1 | 1 | 1
! 2 | 2 | 2
! 3 | | 3
! 4 | | 4
! 5 | | 5
(5 rows)
fetch next from foo;
! i | j | o
! ---+---+---
(0 rows)
fetch next from foo;
! i | j | o
! ---+---+---
(0 rows)
fetch prior from foo;
! i | j | o
! ---+---+---
! 5 | | 5
(1 row)
fetch absolute 1 from foo;
! i | j | o
! ---+---+---
! 1 | 1 | 1
! (1 row)
!
! fetch next from foo;
! i | j | o
! ---+---+---
! 2 | 2 | 2
! (1 row)
!
! fetch next from foo;
! i | j | o
! ---+---+---
! 3 | | 3
! (1 row)
!
! fetch next from foo;
! i | j | o
! ---+---+---
! 4 | | 4
! (1 row)
!
! fetch prior from foo;
! i | j | o
! ---+---+---
! 3 | | 3
! (1 row)
!
! fetch prior from foo;
! i | j | o
! ---+---+---
! 2 | 2 | 2
! (1 row)
!
! fetch prior from foo;
! i | j | o
! ---+---+---
! 1 | 1 | 1
(1 row)
commit;
*************** INSERT INTO foo VALUES(1,1,'Joe');
*** 199,260 ****
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
t1
----
1
(1 row)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! getfoo
! --------
! 1
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY as t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
1 | 1
(1 row)
- -- sql, proretset = t, prorettype = b
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
t1
----
1
1
(2 rows)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
1 | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! getfoo
! --------
! 1
! 1
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
--- 347,407 ----
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
! SELECT * FROM getfoo1(1) AS t1;
t1
----
1
(1 row)
! SELECT * FROM getfoo1(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1);
SELECT * FROM vw_getfoo;
! getfoo1
! ---------
! 1
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1) WITH ORDINALITY as t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
1 | 1
(1 row)
DROP VIEW vw_getfoo;
! -- sql, proretset = t, prorettype = b
! CREATE FUNCTION getfoo2(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo2(1) AS t1;
t1
----
1
1
(2 rows)
! SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
1 | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1);
SELECT * FROM vw_getfoo;
! getfoo2
! ---------
! 1
! 1
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
*************** SELECT * FROM vw_getfoo;
*** 262,295 ****
1 | 2
(2 rows)
- -- sql, proretset = t, prorettype = b
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
t1
-----
Joe
Ed
(2 rows)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
v | o
-----+---
Joe | 1
Ed | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! getfoo
! --------
Joe
Ed
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
-----+---
--- 409,441 ----
1 | 2
(2 rows)
DROP VIEW vw_getfoo;
! -- sql, proretset = t, prorettype = b
! CREATE FUNCTION getfoo3(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo3(1) AS t1;
t1
-----
Joe
Ed
(2 rows)
! SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
v | o
-----+---
Joe | 1
Ed | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1);
SELECT * FROM vw_getfoo;
! getfoo3
! ---------
Joe
Ed
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
-----+---
*************** SELECT * FROM vw_getfoo;
*** 297,319 ****
Ed | 2
(2 rows)
- -- sql, proretset = f, prorettype = c
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
--- 443,464 ----
Ed | 2
(2 rows)
DROP VIEW vw_getfoo;
! -- sql, proretset = f, prorettype = c
! CREATE FUNCTION getfoo4(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo4(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
*************** SELECT * FROM vw_getfoo;
*** 321,352 ****
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
- -- sql, proretset = t, prorettype = c
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
1 | 2 | Ed | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
--- 466,496 ----
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
DROP VIEW vw_getfoo;
! -- sql, proretset = t, prorettype = c
! CREATE FUNCTION getfoo5(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo5(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
! SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
1 | 2 | Ed | 2
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
*************** SELECT * FROM vw_getfoo;
*** 355,361 ****
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
--- 499,505 ----
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
*************** SELECT * FROM vw_getfoo;
*** 363,380 ****
1 | 2 | Ed | 2
(2 rows)
- -- ordinality not supported for returns record yet
- -- sql, proretset = f, prorettype = record
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
--- 507,528 ----
1 | 2 | Ed | 2
(2 rows)
DROP VIEW vw_getfoo;
! -- sql, proretset = f, prorettype = record
! CREATE FUNCTION getfoo6(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo6(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! SELECT * FROM TABLE( getfoo6(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
! fooid | foosubid | fooname | ordinality
! -------+----------+---------+------------
! 1 | 1 | Joe | 1
! (1 row)
!
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo6(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
*************** SELECT * FROM vw_getfoo;
*** 382,399 ****
1 | 1 | Joe
(1 row)
- -- sql, proretset = t, prorettype = record
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
--- 530,563 ----
1 | 1 | Joe
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS
! SELECT * FROM TABLE( getfoo6(1) AS (fooid int, foosubid int, fooname text) )
! WITH ORDINALITY;
! SELECT * FROM vw_getfoo;
! fooid | foosubid | fooname | ordinality
! -------+----------+---------+------------
! 1 | 1 | Joe | 1
! (1 row)
!
! DROP VIEW vw_getfoo;
! -- sql, proretset = t, prorettype = record
! CREATE FUNCTION getfoo7(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo7(1) AS t1(fooid int, foosubid int, fooname text);
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
! SELECT * FROM TABLE( getfoo7(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
! fooid | foosubid | fooname | ordinality
! -------+----------+---------+------------
! 1 | 1 | Joe | 1
! 1 | 2 | Ed | 2
! (2 rows)
!
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo7(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
*************** SELECT * FROM vw_getfoo;
*** 402,455 ****
1 | 2 | Ed
(2 rows)
- -- plpgsql, proretset = f, prorettype = b
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo(1) AS t1;
t1
----
1
(1 row)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
! getfoo
! --------
! 1
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
1 | 1
(1 row)
- -- plpgsql, proretset = f, prorettype = c
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
--- 566,628 ----
1 | 2 | Ed
(2 rows)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS
! SELECT * FROM TABLE( getfoo7(1) AS (fooid int, foosubid int, fooname text) )
! WITH ORDINALITY;
! SELECT * FROM vw_getfoo;
! fooid | foosubid | fooname | ordinality
! -------+----------+---------+------------
! 1 | 1 | Joe | 1
! 1 | 2 | Ed | 2
! (2 rows)
!
! DROP VIEW vw_getfoo;
! -- plpgsql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo8(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo8(1) AS t1;
t1
----
1
(1 row)
! SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
v | o
---+---
1 | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1);
SELECT * FROM vw_getfoo;
! getfoo8
! ---------
! 1
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
v | o
---+---
1 | 1
(1 row)
DROP VIEW vw_getfoo;
! -- plpgsql, proretset = f, prorettype = c
! CREATE FUNCTION getfoo9(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo9(1) AS t1;
fooid | foosubid | fooname
-------+----------+---------
1 | 1 | Joe
(1 row)
! SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
a | b | c | o
---+---+-----+---
1 | 1 | Joe | 1
(1 row)
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1);
SELECT * FROM vw_getfoo;
fooid | foosubid | fooname
-------+----------+---------
*************** SELECT * FROM vw_getfoo;
*** 457,463 ****
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
--- 630,636 ----
(1 row)
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
a | b | c | o
---+---+-----+---
*************** SELECT * FROM vw_getfoo;
*** 465,487 ****
(1 row)
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
! CREATE TEMPORARY SEQUENCE foo_rescan_seq;
CREATE TYPE foo_rescan_t AS (i integer, s bigint);
! CREATE FUNCTION foo_sql(int,int) RETURNS setof foo_rescan_t AS 'SELECT i, nextval(''foo_rescan_seq'') FROM
generate_series($1,$2)i;' LANGUAGE SQL;
-- plpgsql functions use materialize mode
! CREATE FUNCTION foo_mat(int,int) RETURNS setof foo_rescan_t AS 'begin for i in $1..$2 loop return next (i,
nextval(''foo_rescan_seq''));end loop; end;' LANGUAGE plpgsql;
--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
-- is on the inner path of a nestloop join
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) ON (r+i)<100;
--- 638,719 ----
(1 row)
DROP VIEW vw_getfoo;
! -- mix 'n match kinds, to exercise expandRTE and related logic
! select * from table(getfoo1(1),getfoo2(1),getfoo3(1),getfoo4(1),getfoo5(1),
! getfoo6(1) AS (fooid int, foosubid int, fooname text),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo8(1),getfoo9(1))
! with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
! a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u
! ---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+---+-----+---
! 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1
! | 1 | Ed | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | | 2
! (2 rows)
!
! select * from table(getfoo9(1),getfoo8(1),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo6(1) AS (fooid int, foosubid int, fooname text),
! getfoo5(1),getfoo4(1),getfoo3(1),getfoo2(1),getfoo1(1))
! with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
! a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u
! ---+---+-----+---+---+---+-----+---+---+-----+---+---+-----+---+---+-----+-----+---+---+---
! 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | Joe | 1 | 1 | 1
! | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | Ed | 1 | | 2
! (2 rows)
!
! create temporary view vw_foo as
! select * from table(getfoo9(1),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo1(1))
! with ordinality as t1(a,b,c,d,e,f,g,n);
! select * from vw_foo;
! a | b | c | d | e | f | g | n
! ---+---+-----+---+---+-----+---+---
! 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1
! | | | 1 | 2 | Ed | | 2
! (2 rows)
!
! select pg_get_viewdef('vw_foo');
! pg_get_viewdef
!
--------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT t1.a,
+
! t1.b,
+
! t1.c,
+
! t1.d,
+
! t1.e,
+
! t1.f,
+
! t1.g,
+
! t1.n
+
! FROM TABLE(getfoo9(1), getfoo7(1) AS (fooid integer, foosubid integer, fooname text), getfoo1(1)) WITH ORDINALITY
t1(a,b, c, d, e, f, g, n);
! (1 row)
!
! drop view vw_foo;
! DROP FUNCTION getfoo1(int);
! DROP FUNCTION getfoo2(int);
! DROP FUNCTION getfoo3(int);
! DROP FUNCTION getfoo4(int);
! DROP FUNCTION getfoo5(int);
! DROP FUNCTION getfoo6(int);
! DROP FUNCTION getfoo7(int);
! DROP FUNCTION getfoo8(int);
! DROP FUNCTION getfoo9(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
! CREATE TEMPORARY SEQUENCE foo_rescan_seq1;
! CREATE TEMPORARY SEQUENCE foo_rescan_seq2;
CREATE TYPE foo_rescan_t AS (i integer, s bigint);
! CREATE FUNCTION foo_sql(int,int) RETURNS setof foo_rescan_t AS 'SELECT i, nextval(''foo_rescan_seq1'') FROM
generate_series($1,$2)i;' LANGUAGE SQL;
-- plpgsql functions use materialize mode
! CREATE FUNCTION foo_mat(int,int) RETURNS setof foo_rescan_t AS 'begin for i in $1..$2 loop return next (i,
nextval(''foo_rescan_seq2''));end loop; end;' LANGUAGE plpgsql;
--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
-- is on the inner path of a nestloop join
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) ON (r+i)<100;
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 498,507 ****
3 | 13 | 3
(9 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
--- 730,739 ----
3 | 13 | 3
(9 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 518,527 ****
3 | 13 | 3 | 3
(9 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) ON (r+i)<100;
--- 750,759 ----
3 | 13 | 3 | 3
(9 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) ON (r+i)<100;
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 538,547 ****
3 | 13 | 3
(9 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
--- 770,779 ----
3 | 13 | 3
(9 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 558,563 ****
--- 790,815 ----
3 | 13 | 3 | 3
(9 rows)
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ setval | setval
+ --------+--------
+ 1 | 1
+ (1 row)
+
+ SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN TABLE( foo_sql(11,13), foo_mat(11,13) ) WITH ORDINALITY AS
f(i1,s1,i2,s2,o)ON (r+i1+i2)<100;
+ r | i1 | s1 | i2 | s2 | o
+ ---+----+----+----+----+---
+ 1 | 11 | 1 | 11 | 1 | 1
+ 1 | 12 | 2 | 12 | 2 | 2
+ 1 | 13 | 3 | 13 | 3 | 3
+ 2 | 11 | 1 | 11 | 1 | 1
+ 2 | 12 | 2 | 12 | 2 | 2
+ 2 | 13 | 3 | 13 | 3 | 3
+ 3 | 11 | 1 | 11 | 1 | 1
+ 3 | 12 | 2 | 12 | 2 | 2
+ 3 | 13 | 3 | 13 | 3 | 3
+ (9 rows)
+
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
r | i
---+----
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 615,624 ****
(9 rows)
--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13);
--- 867,876 ----
(9 rows)
--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 632,641 ****
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
--- 884,893 ----
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 649,658 ****
3 | 13 | 6 | 1
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r);
--- 901,910 ----
3 | 13 | 6 | 1
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 666,675 ****
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
--- 918,927 ----
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 683,692 ****
3 | 13 | 6 | 3
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2);
--- 935,944 ----
3 | 13 | 6 | 3
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2);
*************** SELECT * FROM (VALUES (11,12),(13,15),(1
*** 704,713 ****
16 | 20 | 20 | 10
(10 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
--- 956,965 ----
16 | 20 | 20 | 10
(10 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (11,12),(13,15),(1
*** 725,734 ****
16 | 20 | 20 | 10 | 5
(10 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13);
--- 977,986 ----
16 | 20 | 20 | 10 | 5
(10 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 742,751 ****
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
--- 994,1003 ----
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 759,768 ****
3 | 13 | 6 | 1
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r);
--- 1011,1020 ----
3 | 13 | 6 | 1
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 776,785 ****
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
--- 1028,1037 ----
3 | 13 | 6
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r),
*** 793,802 ****
3 | 13 | 6 | 3
(6 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2);
--- 1045,1054 ----
3 | 13 | 6 | 3
(6 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2);
*************** SELECT * FROM (VALUES (11,12),(13,15),(1
*** 814,823 ****
16 | 20 | 20 | 10
(10 rows)
! SELECT setval('foo_rescan_seq',1,false);
! setval
! --------
! 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
--- 1066,1075 ----
16 | 20 | 20 | 10
(10 rows)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
! setval | setval
! --------+--------
! 1 | 1
(1 row)
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
*************** SELECT * FROM (VALUES (11,12),(13,15),(1
*** 835,840 ****
--- 1087,1168 ----
16 | 20 | 20 | 10 | 5
(10 rows)
+ -- selective rescan of multiple functions:
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ setval | setval
+ --------+--------
+ 1 | 1
+ (1 row)
+
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(11,11), foo_mat(10+r,13) );
+ r | i | s | i | s
+ ---+----+---+----+---
+ 1 | 11 | 1 | 11 | 1
+ 1 | | | 12 | 2
+ 1 | | | 13 | 3
+ 2 | 11 | 1 | 12 | 4
+ 2 | | | 13 | 5
+ 3 | 11 | 1 | 13 | 6
+ (6 rows)
+
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ setval | setval
+ --------+--------
+ 1 | 1
+ (1 row)
+
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(10+r,13), foo_mat(11,11) );
+ r | i | s | i | s
+ ---+----+---+----+---
+ 1 | 11 | 1 | 11 | 1
+ 1 | 12 | 2 | |
+ 1 | 13 | 3 | |
+ 2 | 12 | 4 | 11 | 1
+ 2 | 13 | 5 | |
+ 3 | 13 | 6 | 11 | 1
+ (6 rows)
+
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ setval | setval
+ --------+--------
+ 1 | 1
+ (1 row)
+
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(10+r,13), foo_mat(10+r,13) );
+ r | i | s | i | s
+ ---+----+---+----+---
+ 1 | 11 | 1 | 11 | 1
+ 1 | 12 | 2 | 12 | 2
+ 1 | 13 | 3 | 13 | 3
+ 2 | 12 | 4 | 12 | 4
+ 2 | 13 | 5 | 13 | 5
+ 3 | 13 | 6 | 13 | 6
+ (6 rows)
+
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ setval | setval
+ --------+--------
+ 1 | 1
+ (1 row)
+
+ SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, TABLE( foo_sql(10+r1,13), foo_mat(10+r2,13) );
+ r1 | r2 | i | s | i | s
+ ----+----+----+----+----+---
+ 1 | 1 | 11 | 1 | 11 | 1
+ 1 | 1 | 12 | 2 | 12 | 2
+ 1 | 1 | 13 | 3 | 13 | 3
+ 1 | 2 | 11 | 4 | 12 | 4
+ 1 | 2 | 12 | 5 | 13 | 5
+ 1 | 2 | 13 | 6 | |
+ 1 | 3 | 11 | 7 | 13 | 6
+ 1 | 3 | 12 | 8 | |
+ 1 | 3 | 13 | 9 | |
+ 2 | 2 | 12 | 10 | 12 | 7
+ 2 | 2 | 13 | 11 | 13 | 8
+ 2 | 3 | 12 | 12 | 13 | 9
+ 2 | 3 | 13 | 13 | |
+ (13 rows)
+
SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
r | i
---+----
*************** SELECT * FROM (VALUES (1),(2),(3)) v1(r1
*** 1072,1078 ****
DROP FUNCTION foo_sql(int,int);
DROP FUNCTION foo_mat(int,int);
! DROP SEQUENCE foo_rescan_seq;
--
-- Test cases involving OUT parameters
--
--- 1400,1407 ----
DROP FUNCTION foo_sql(int,int);
DROP FUNCTION foo_mat(int,int);
! DROP SEQUENCE foo_rescan_seq1;
! DROP SEQUENCE foo_rescan_seq2;
--
-- Test cases involving OUT parameters
--
*************** SELECT * FROM get_users() WITH ORDINALIT
*** 1566,1571 ****
--- 1895,1915 ----
id2 | email2 | t | 2
(2 rows)
+ -- multiple functions vs. dropped columns
+ SELECT * FROM TABLE(generate_series(10,11), get_users()) WITH ORDINALITY;
+ generate_series | userid | email | enabled | ordinality
+ -----------------+--------+--------+---------+------------
+ 10 | id | email | t | 1
+ 11 | id2 | email2 | t | 2
+ (2 rows)
+
+ SELECT * FROM TABLE(get_users(), generate_series(10,11)) WITH ORDINALITY;
+ userid | email | enabled | generate_series | ordinality
+ --------+--------+---------+-----------------+------------
+ id | email | t | 10 | 1
+ id2 | email2 | t | 11 | 2
+ (2 rows)
+
drop function get_first_user();
drop function get_users();
drop table users;
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index e82a1d5..ce0c9af 100644
*** a/src/test/regress/sql/rangefuncs.sql
--- b/src/test/regress/sql/rangefuncs.sql
*************** create temporary view vw_ord as select *
*** 21,29 ****
select * from vw_ord;
select definition from pg_views where viewname='vw_ord';
drop view vw_ord;
! -- ordinality vs. rewind and reverse scan
begin;
! declare foo scroll cursor for select * from generate_series(1,5) with ordinality as g(i,o);
fetch all from foo;
fetch backward all from foo;
fetch all from foo;
--- 21,55 ----
select * from vw_ord;
select definition from pg_views where viewname='vw_ord';
drop view vw_ord;
!
! -- multiple functions
! select * from table(foot(1),foot(2)) with ordinality as z(a,b,c,d,ord);
! create temporary view vw_ord as select * from (values (1)) v(n) join table(foot(1),foot(2)) with ordinality as
z(a,b,c,d,ord)on (n=ord);
! select * from vw_ord;
! select definition from pg_views where viewname='vw_ord';
! drop view vw_ord;
!
! -- expansions of unnest()
! select * from unnest(array[10,20],array['foo','bar'],array[1.0]);
! select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord);
! select * from table(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord);
! select * from table(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as
z(a,b,c,ord);
! create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c);
! select * from vw_ord;
! select definition from pg_views where viewname='vw_ord';
! drop view vw_ord;
! create temporary view vw_ord as select * from table(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c);
! select * from vw_ord;
! select definition from pg_views where viewname='vw_ord';
! drop view vw_ord;
! create temporary view vw_ord as select * from table(unnest(array[10,20],array['foo','bar']), generate_series(1,2)) as
z(a,b,c);
! select * from vw_ord;
! select definition from pg_views where viewname='vw_ord';
! drop view vw_ord;
!
! -- ordinality and multiple functions vs. rewind and reverse scan
begin;
! declare foo scroll cursor for select * from table(generate_series(1,5),generate_series(1,2)) with ordinality as
g(i,j,o);
fetch all from foo;
fetch backward all from foo;
fetch all from foo;
*************** fetch next from foo;
*** 31,36 ****
--- 57,68 ----
fetch next from foo;
fetch prior from foo;
fetch absolute 1 from foo;
+ fetch next from foo;
+ fetch next from foo;
+ fetch next from foo;
+ fetch prior from foo;
+ fetch prior from foo;
+ fetch prior from foo;
commit;
-- function with implicit LATERAL
*************** INSERT INTO foo VALUES(1,2,'Ed');
*** 57,189 ****
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY as t1(v,o);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = b
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = b
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
-- sql, proretset = f, prorettype = c
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = c
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
- -- ordinality not supported for returns record yet
-- sql, proretset = f, prorettype = record
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = record
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
-- plpgsql, proretset = f, prorettype = b
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid =
$1;RETURN fooint; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
-- plpgsql, proretset = f, prorettype = c
! DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
! CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo(1) AS t1;
! SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
-
DROP VIEW vw_getfoo;
! DROP FUNCTION getfoo(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
! CREATE TEMPORARY SEQUENCE foo_rescan_seq;
CREATE TYPE foo_rescan_t AS (i integer, s bigint);
! CREATE FUNCTION foo_sql(int,int) RETURNS setof foo_rescan_t AS 'SELECT i, nextval(''foo_rescan_seq'') FROM
generate_series($1,$2)i;' LANGUAGE SQL;
-- plpgsql functions use materialize mode
! CREATE FUNCTION foo_mat(int,int) RETURNS setof foo_rescan_t AS 'begin for i in $1..$2 loop return next (i,
nextval(''foo_rescan_seq''));end loop; end;' LANGUAGE plpgsql;
--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
-- is on the inner path of a nestloop join
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) ON (r+i)<100;
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) ON (r+i)<100;
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
--- 89,257 ----
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
! SELECT * FROM getfoo1(1) AS t1;
! SELECT * FROM getfoo1(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1) WITH ORDINALITY as t1(v,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = t, prorettype = b
! CREATE FUNCTION getfoo2(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo2(1) AS t1;
! SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = t, prorettype = b
! CREATE FUNCTION getfoo3(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo3(1) AS t1;
! SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = f, prorettype = c
! CREATE FUNCTION getfoo4(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo4(1) AS t1;
! SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = t, prorettype = c
! CREATE FUNCTION getfoo5(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo5(1) AS t1;
! SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = f, prorettype = record
! CREATE FUNCTION getfoo6(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo6(1) AS t1(fooid int, foosubid int, fooname text);
! SELECT * FROM TABLE( getfoo6(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo6(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS
+ SELECT * FROM TABLE( getfoo6(1) AS (fooid int, foosubid int, fooname text) )
+ WITH ORDINALITY;
+ SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- sql, proretset = t, prorettype = record
! CREATE FUNCTION getfoo7(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
! SELECT * FROM getfoo7(1) AS t1(fooid int, foosubid int, fooname text);
! SELECT * FROM TABLE( getfoo7(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo7(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS
+ SELECT * FROM TABLE( getfoo7(1) AS (fooid int, foosubid int, fooname text) )
+ WITH ORDINALITY;
+ SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- plpgsql, proretset = f, prorettype = b
! CREATE FUNCTION getfoo8(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid
=$1; RETURN fooint; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo8(1) AS t1;
! SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getfoo;
+ DROP VIEW vw_getfoo;
-- plpgsql, proretset = f, prorettype = c
! CREATE FUNCTION getfoo9(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE
fooid= $1; RETURN footup; END;' LANGUAGE plpgsql;
! SELECT * FROM getfoo9(1) AS t1;
! SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
! CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getfoo;
DROP VIEW vw_getfoo;
!
! -- mix 'n match kinds, to exercise expandRTE and related logic
!
! select * from table(getfoo1(1),getfoo2(1),getfoo3(1),getfoo4(1),getfoo5(1),
! getfoo6(1) AS (fooid int, foosubid int, fooname text),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo8(1),getfoo9(1))
! with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
! select * from table(getfoo9(1),getfoo8(1),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo6(1) AS (fooid int, foosubid int, fooname text),
! getfoo5(1),getfoo4(1),getfoo3(1),getfoo2(1),getfoo1(1))
! with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
!
! create temporary view vw_foo as
! select * from table(getfoo9(1),
! getfoo7(1) AS (fooid int, foosubid int, fooname text),
! getfoo1(1))
! with ordinality as t1(a,b,c,d,e,f,g,n);
! select * from vw_foo;
! select pg_get_viewdef('vw_foo');
! drop view vw_foo;
!
! DROP FUNCTION getfoo1(int);
! DROP FUNCTION getfoo2(int);
! DROP FUNCTION getfoo3(int);
! DROP FUNCTION getfoo4(int);
! DROP FUNCTION getfoo5(int);
! DROP FUNCTION getfoo6(int);
! DROP FUNCTION getfoo7(int);
! DROP FUNCTION getfoo8(int);
! DROP FUNCTION getfoo9(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
! CREATE TEMPORARY SEQUENCE foo_rescan_seq1;
! CREATE TEMPORARY SEQUENCE foo_rescan_seq2;
CREATE TYPE foo_rescan_t AS (i integer, s bigint);
! CREATE FUNCTION foo_sql(int,int) RETURNS setof foo_rescan_t AS 'SELECT i, nextval(''foo_rescan_seq1'') FROM
generate_series($1,$2)i;' LANGUAGE SQL;
-- plpgsql functions use materialize mode
! CREATE FUNCTION foo_mat(int,int) RETURNS setof foo_rescan_t AS 'begin for i in $1..$2 loop return next (i,
nextval(''foo_rescan_seq2''));end loop; end;' LANGUAGE plpgsql;
--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
-- is on the inner path of a nestloop join
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) ON (r+i)<100;
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) ON (r+i)<100;
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN TABLE( foo_sql(11,13), foo_mat(11,13) ) WITH ORDINALITY AS
f(i1,s1,i2,s2,o)ON (r+i1+i2)<100;
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
*************** SELECT * FROM (VALUES (1),(2),(3)) v(r)
*** 193,224 ****
--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2);
! SELECT setval('foo_rescan_seq',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o);
--- 261,304 ----
--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2);
! SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
+ -- selective rescan of multiple functions:
+
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(11,11), foo_mat(10+r,13) );
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(10+r,13), foo_mat(11,11) );
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ SELECT * FROM (VALUES (1),(2),(3)) v(r), TABLE( foo_sql(10+r,13), foo_mat(10+r,13) );
+
+ SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
+ SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, TABLE( foo_sql(10+r1,13), foo_mat(10+r2,13) );
+
SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o);
*************** SELECT * FROM (VALUES (1),(2),(3)) v1(r1
*** 242,248 ****
DROP FUNCTION foo_sql(int,int);
DROP FUNCTION foo_mat(int,int);
! DROP SEQUENCE foo_rescan_seq;
--
-- Test cases involving OUT parameters
--- 322,329 ----
DROP FUNCTION foo_sql(int,int);
DROP FUNCTION foo_mat(int,int);
! DROP SEQUENCE foo_rescan_seq1;
! DROP SEQUENCE foo_rescan_seq2;
--
-- Test cases involving OUT parameters
*************** language sql stable;
*** 466,471 ****
--- 547,555 ----
SELECT get_users();
SELECT * FROM get_users();
SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes
+ -- multiple functions vs. dropped columns
+ SELECT * FROM TABLE(generate_series(10,11), get_users()) WITH ORDINALITY;
+ SELECT * FROM TABLE(get_users(), generate_series(10,11)) WITH ORDINALITY;
drop function get_first_user();
drop function get_users();
pgsql-hackers by date: