From c62736cc37f6812d1ebb41ea5a86ffe60564a1f0 Mon Sep 17 00:00:00 2001 From: Greg Stark Date: Mon, 29 Jul 2013 16:38:01 +0100 Subject: [PATCH] Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF) Author: Andrew Gierth, David Fetter Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost --- doc/src/sgml/func.sgml | 44 +- doc/src/sgml/ref/select.sgml | 47 +- src/backend/access/common/tupdesc.c | 34 + src/backend/executor/nodeFunctionscan.c | 176 +++- src/backend/nodes/copyfuncs.c | 3 + src/backend/nodes/equalfuncs.c | 2 + src/backend/nodes/makefuncs.c | 32 +- src/backend/nodes/outfuncs.c | 3 + src/backend/nodes/readfuncs.c | 1 + src/backend/optimizer/plan/createplan.c | 7 +- src/backend/optimizer/util/clauses.c | 5 + src/backend/parser/gram.y | 28 +- src/backend/parser/parse_relation.c | 176 +++- src/backend/parser/parser.c | 5 +- src/backend/utils/adt/ruleutils.c | 2 + src/include/access/tupdesc.h | 1 + src/include/nodes/execnodes.h | 10 +- src/include/nodes/parsenodes.h | 22 +- src/include/nodes/plannodes.h | 1 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/rangefuncs.out | 1002 ++++++++++++++++++---- src/test/regress/sql/rangefuncs.sql | 233 +++-- 22 files changed, 1499 insertions(+), 336 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 528197e4bc..34c5c2a2d6 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -13278,7 +13278,7 @@ select $1[i][j] generate_subscripts($1,2) g2(j); $$ LANGUAGE sql IMMUTABLE; CREATE FUNCTION -postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); +SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); unnest2 --------- 1 @@ -13286,6 +13286,48 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); 3 4 (4 rows) + + + + + ordinality + + + + When a function in the FROM clause is suffixed by + WITH ORDINALITY, a bigint column is appended + to the output which starts from 1 and increments by 1 for each row of the + function's output. This is most useful in the case of set returning functions + such as UNNEST(). This functionality is available for functions returning + composite types or using OUT parameters, but not when using + a function returning RECORD with an explicit column + definition list. + + +-- set returning function WITH ORDINALITY +SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n); + ls | n +-----------------+---- + pg_serial | 1 + pg_twophase | 2 + postmaster.opts | 3 + pg_notify | 4 + postgresql.conf | 5 + pg_tblspc | 6 + logfile | 7 + base | 8 + postmaster.pid | 9 + pg_ident.conf | 10 + global | 11 + pg_clog | 12 + pg_snapshots | 13 + pg_multixact | 14 + PG_VERSION | 15 + pg_xlog | 16 + pg_hba.conf | 17 + pg_stat_tmp | 18 + pg_subtrans | 19 +(19 rows) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index b0cec1421c..42cfc28a5e 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -52,7 +52,8 @@ SELECT [ ALL | DISTINCT [ ON ( expressiontable_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] - [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] + [ LATERAL ] function_name ( [ argument [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] + [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] ) [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] @@ -368,18 +369,40 @@ TABLE [ ONLY ] table_name [ * ] clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though its output were created as a temporary table for the - duration of this single SELECT command. An - alias can also be used. If an alias is written, a column alias - list can also be written to provide substitute names for one - or more attributes of the function's composite return type. If - the function has been defined as returning the record - data type, then an alias or the key word AS must - be present, followed by a column definition list in the form - ( SELECT command. + When the optional WITH ORDINALITY is + appended to the function call, a new column is appended after + all the function call's columns with numbering for each row. + For example: + +SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY; + unnest | ordinality +--------+---------- + a | 1 + b | 2 + c | 3 + d | 4 + e | 5 + f | 6 +(6 rows) + + An alias can also be used. If an alias is written, a column + alias list can also be written to provide substitute names for + one or more attributes of the function's composite return + type, including the column added by ORDINALITY + if present. + + + + If the function has been defined as returning the + record data type, then an alias or the key word + AS must be present, followed by a column + definition list in the form ( column_name data_type , ... - ). The column definition list must match the actual - number and types of columns returned by the function. + class="parameter">data_type , ... + ). The column definition list must match the + actual number and types of columns returned by the function. + ORDINALITY does not work in this case. diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index fb5c199c0c..11c31d8fe9 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -157,6 +157,40 @@ CreateTupleDescCopy(TupleDesc tupdesc) return desc; } +/* + * 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 diff --git a/src/backend/executor/nodeFunctionscan.c b/src/backend/executor/nodeFunctionscan.c index 24325fee6e..423e02f354 100644 --- a/src/backend/executor/nodeFunctionscan.c +++ b/src/backend/executor/nodeFunctionscan.c @@ -25,7 +25,7 @@ #include "executor/nodeFunctionscan.h" #include "funcapi.h" #include "nodes/nodeFuncs.h" - +#include "catalog/pg_type.h" static TupleTableSlot *FunctionNext(FunctionScanState *node); @@ -42,10 +42,37 @@ static TupleTableSlot *FunctionNext(FunctionScanState *node); static TupleTableSlot * FunctionNext(FunctionScanState *node) { - TupleTableSlot *slot; 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 @@ -64,19 +91,62 @@ FunctionNext(FunctionScanState *node) node->tuplestorestate = tuplestorestate = ExecMakeTableFunctionResult(node->funcexpr, node->ss.ps.ps_ExprContext, - node->tupdesc, + node->func_tupdesc, node->eflags & EXEC_FLAG_BACKWARD); } /* * Get the next tuple from tuplestore. Return NULL if no more tuples. */ - slot = node->ss.ss_ScanTupleSlot; (void) tuplestore_gettupleslot(tuplestorestate, ScanDirectionIsForward(direction), false, - slot); - return slot; + 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 + * move off either end of the result by 1 (and no more than 1) without + * 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; } /* @@ -116,7 +186,8 @@ ExecInitFunctionScan(FunctionScan *node, EState *estate, int eflags) FunctionScanState *scanstate; Oid funcrettype; TypeFuncClass functypclass; - TupleDesc tupdesc = NULL; + TupleDesc func_tupdesc = NULL; + TupleDesc scan_tupdesc = NULL; /* check for unsupported flags */ Assert(!(eflags & EXEC_FLAG_MARK)); @@ -148,6 +219,16 @@ ExecInitFunctionScan(FunctionScan *node, EState *estate, int eflags) ExecInitResultTupleSlot(estate, &scanstate->ss.ps); 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 */ @@ -159,42 +240,55 @@ ExecInitFunctionScan(FunctionScan *node, EState *estate, int eflags) (PlanState *) scanstate); /* - * Now determine if the function returns a simple or composite type, and - * build an appropriate tupdesc. + * 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, - &tupdesc); + &func_tupdesc); if (functypclass == TYPEFUNC_COMPOSITE) { /* Composite data type, e.g. a table's row type */ - Assert(tupdesc); + 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 */ - tupdesc = CreateTupleDescCopy(tupdesc); + func_tupdesc = CreateTupleDescCopy(func_tupdesc); } else if (functypclass == TYPEFUNC_SCALAR) { /* Base data type, i.e. scalar */ char *attname = strVal(linitial(node->funccolnames)); - tupdesc = CreateTemplateTupleDesc(1, false); - TupleDescInitEntry(tupdesc, + func_tupdesc = CreateTemplateTupleDesc(1, false); + TupleDescInitEntry(func_tupdesc, (AttrNumber) 1, attname, funcrettype, -1, 0); - TupleDescInitEntryCollation(tupdesc, + TupleDescInitEntryCollation(func_tupdesc, (AttrNumber) 1, exprCollation(node->funcexpr)); } else if (functypclass == TYPEFUNC_RECORD) { - tupdesc = BuildDescFromLists(node->funccolnames, - node->funccoltypes, - node->funccoltypmods, - node->funccolcollations); + func_tupdesc = BuildDescFromLists(node->funccolnames, + node->funccoltypes, + node->funccoltypmods, + node->funccolcollations); } else { @@ -207,15 +301,47 @@ ExecInitFunctionScan(FunctionScan *node, EState *estate, int eflags) * function should do this for itself, but let's cover things in case it * doesn't.) */ - BlessTupleDesc(tupdesc); + 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); - scanstate->tupdesc = tupdesc; - ExecAssignScanType(&scanstate->ss, tupdesc); + 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); @@ -249,6 +375,8 @@ ExecEndFunctionScan(FunctionScanState *node) */ ExecClearTuple(node->ss.ps.ps_ResultTupleSlot); ExecClearTuple(node->ss.ss_ScanTupleSlot); + if (node->func_slot) + ExecClearTuple(node->func_slot); /* * Release tuplestore resources @@ -268,9 +396,13 @@ 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. */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bcc6496a95..71e305804d 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -509,6 +509,7 @@ _copyFunctionScan(const FunctionScan *from) COPY_NODE_FIELD(funccoltypes); COPY_NODE_FIELD(funccoltypmods); COPY_NODE_FIELD(funccolcollations); + COPY_SCALAR_FIELD(funcordinality); return newnode; } @@ -1983,6 +1984,7 @@ _copyRangeTblEntry(const RangeTblEntry *from) 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); COPY_STRING_FIELD(ctename); @@ -2296,6 +2298,7 @@ _copyRangeFunction(const RangeFunction *from) { RangeFunction *newnode = makeNode(RangeFunction); + COPY_SCALAR_FIELD(ordinality); COPY_SCALAR_FIELD(lateral); COPY_NODE_FIELD(funccallnode); COPY_NODE_FIELD(alias); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 7f9737ee8e..3183ccff4d 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2126,6 +2126,7 @@ _equalRangeSubselect(const RangeSubselect *a, const RangeSubselect *b) static bool _equalRangeFunction(const RangeFunction *a, const RangeFunction *b) { + COMPARE_SCALAR_FIELD(ordinality); COMPARE_SCALAR_FIELD(lateral); COMPARE_NODE_FIELD(funccallnode); COMPARE_NODE_FIELD(alias); @@ -2234,6 +2235,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) 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); COMPARE_STRING_FIELD(ctename); diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 0f8a282ec8..b742ec9532 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -126,6 +126,10 @@ makeVarFromTargetEntry(Index varno, * returning a non-composite result type, we produce a normal Var referencing * 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, @@ -151,9 +155,33 @@ makeWholeRowVar(RangeTblEntry *rte, InvalidOid, varlevelsup); 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 (type_is_rowtype(toid)) + + if (rte->funcordinality) + { + /* ORDINALITY always produces an anonymous RECORD result */ + result = makeVar(varno, + InvalidAttrNumber, + RECORDOID, + -1, + InvalidOid, + varlevelsup); + } + else if (type_is_rowtype(toid)) { /* func returns composite; same as relation case */ result = makeVar(varno, @@ -184,8 +212,8 @@ makeWholeRowVar(RangeTblEntry *rte, varlevelsup); } break; - default: + default: /* * RTE is a join, subselect, or VALUES. We represent this as a * whole-row Var of RECORD type. (Note that in most cases the Var diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 48cd9dcb8d..bad22399bf 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -521,6 +521,7 @@ _outFunctionScan(StringInfo str, const FunctionScan *node) WRITE_NODE_FIELD(funccoltypes); WRITE_NODE_FIELD(funccoltypmods); WRITE_NODE_FIELD(funccolcollations); + WRITE_BOOL_FIELD(funcordinality); } static void @@ -2382,6 +2383,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) WRITE_NODE_FIELD(funccoltypes); WRITE_NODE_FIELD(funccoltypmods); WRITE_NODE_FIELD(funccolcollations); + WRITE_BOOL_FIELD(funcordinality); break; case RTE_VALUES: WRITE_NODE_FIELD(values_lists); @@ -2614,6 +2616,7 @@ _outRangeFunction(StringInfo str, const RangeFunction *node) { WRITE_NODE_TYPE("RANGEFUNCTION"); + WRITE_BOOL_FIELD(ordinality); WRITE_BOOL_FIELD(lateral); WRITE_NODE_FIELD(funccallnode); WRITE_NODE_FIELD(alias); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index dc9cb3ebd2..aad63e58cf 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1223,6 +1223,7 @@ _readRangeTblEntry(void) READ_NODE_FIELD(funccoltypes); READ_NODE_FIELD(funccoltypmods); READ_NODE_FIELD(funccolcollations); + READ_BOOL_FIELD(funcordinality); break; case RTE_VALUES: READ_NODE_FIELD(values_lists); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 7fed5e9794..617cb19a04 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -115,8 +115,8 @@ static BitmapHeapScan *make_bitmap_heapscan(List *qptlist, static TidScan *make_tidscan(List *qptlist, List *qpqual, Index scanrelid, List *tidquals); static FunctionScan *make_functionscan(List *qptlist, List *qpqual, - Index scanrelid, Node *funcexpr, List *funccolnames, - List *funccoltypes, List *funccoltypmods, + 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); @@ -1733,6 +1733,7 @@ create_functionscan_plan(PlannerInfo *root, Path *best_path, scan_plan = make_functionscan(tlist, scan_clauses, scan_relid, funcexpr, + rte->funcordinality, rte->eref->colnames, rte->funccoltypes, rte->funccoltypmods, @@ -3366,6 +3367,7 @@ make_functionscan(List *qptlist, List *qpqual, Index scanrelid, Node *funcexpr, + bool ordinality, List *funccolnames, List *funccoltypes, List *funccoltypmods, @@ -3381,6 +3383,7 @@ make_functionscan(List *qptlist, plan->righttree = NULL; node->scan.scanrelid = scanrelid; node->funcexpr = funcexpr; + node->funcordinality = ordinality; node->funccolnames = funccolnames; node->funccoltypes = funccoltypes; node->funccoltypmods = funccoltypmods; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 506e9d49fc..76c032c569 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -4452,10 +4452,15 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) */ check_stack_depth(); + /* Fail if the caller wanted ORDINALITY - we don't implement that here. */ + 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; /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index d8d2bdf09a..22e82ba146 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -566,7 +566,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR - ORDER OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER + ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY @@ -609,8 +609,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ -%token NULLS_FIRST NULLS_LAST WITH_TIME - +%token NULLS_FIRST NULLS_LAST WITH_ORDINALITY WITH_TIME /* Precedence: lowest to highest */ %nonassoc SET /* see relation_expr_opt_alias */ @@ -9588,20 +9587,42 @@ table_ref: relation_expr opt_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 { RangeSubselect *n = makeNode(RangeSubselect); @@ -12575,6 +12596,7 @@ unreserved_keyword: | OPERATOR | OPTION | OPTIONS + | ORDINALITY | OVER | OWNED | OWNER diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index b2b88fc6a9..39922d32c5 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -787,18 +787,24 @@ markVarForSelectPriv(ParseState *pstate, Var *var, RangeTblEntry *rte) * buildRelationAliases * Construct the eref column name list for a relation RTE. * This code is also used for the case of a function RTE returning - * a named composite type. + * a named composite type or a registered RECORD type. * * tupdesc: the physical column information * alias: the user-supplied alias, or NULL if none * eref: the eref Alias to store column names in + * ordinality: true if an ordinality column is to be added * * eref->colnames is filled in. Also, alias->colnames is rebuilt to insert * empty strings for any dropped columns, so that it will be one-to-one with * physical column numbers. + * + * If we add an ordinality column, its colname comes from the alias if there + * is one, otherwise we default it. (We don't add it to alias->colnames.) + * + * It is an error for there to be more aliases present than required. */ static void -buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref) +buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref, bool ordinality) { int maxattrs = tupdesc->natts; ListCell *aliaslc; @@ -850,12 +856,33 @@ buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref) eref->colnames = lappend(eref->colnames, attrname); } + /* tack on the ordinality column at the end */ + if (ordinality) + { + Value *attrname; + + if (aliaslc) + { + attrname = (Value *) lfirst(aliaslc); + aliaslc = lnext(aliaslc); + alias->colnames = lappend(alias->colnames, attrname); + } + else + { + attrname = makeString(pstrdup("ordinality")); + } + + eref->colnames = lappend(eref->colnames, attrname); + } + /* Too many user-supplied aliases? */ if (aliaslc) ereport(ERROR, (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), errmsg("table \"%s\" has %d columns available but %d columns specified", - eref->aliasname, maxattrs - numdropped, numaliases))); + eref->aliasname, + maxattrs - numdropped + (ordinality ? 1 : 0), + numaliases))); } /* @@ -867,48 +894,60 @@ buildRelationAliases(TupleDesc tupdesc, Alias *alias, Alias *eref) * 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) + Alias *alias, Alias *eref, bool ordinality) { - char *pname; - Assert(eref->colnames == NIL); /* Use user-specified column alias if there is one. */ if (alias && alias->colnames != NIL) { - if (list_length(alias->colnames) != 1) + if (list_length(alias->colnames) > (ordinality ? 2 : 1)) ereport(ERROR, (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), errmsg("too many column aliases specified for function %s", funcname))); + eref->colnames = copyObject(alias->colnames); - return; } - - /* - * If the expression is a simple function call, and the function has a - * single OUT parameter that is named, use the parameter's name. - */ - if (funcexpr && IsA(funcexpr, FuncExpr)) + else { - pname = get_func_result_name(((FuncExpr *) funcexpr)->funcid); - if (pname) - { - eref->colnames = list_make1(makeString(pname)); - return; - } + char *pname = NULL; + + /* + * If the expression is a simple function call, and the function has a + * single OUT parameter that is named, use the parameter's name. + */ + if (funcexpr && IsA(funcexpr, FuncExpr)) + pname = get_func_result_name(((FuncExpr *) funcexpr)->funcid); + + /* + * Otherwise, use the previously-determined alias name provided by the + * caller (which is not necessarily the function name!) + */ + if (!pname) + pname = eref->aliasname; + + eref->colnames = list_make1(makeString(pname)); } - /* - * Otherwise use the previously-determined alias (not necessarily the - * function name!) - */ - eref->colnames = list_make1(makeString(eref->aliasname)); + /* 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"))); + + return; } /* @@ -1004,7 +1043,7 @@ addRangeTableEntry(ParseState *pstate, * and/or actual column names. */ rte->eref = makeAlias(refname, NIL); - buildRelationAliases(rel->rd_att, alias, rte->eref); + buildRelationAliases(rel->rd_att, alias, rte->eref, false); /* * Drop the rel refcount, but keep the access lock till end of transaction @@ -1064,7 +1103,7 @@ addRangeTableEntryForRelation(ParseState *pstate, * and/or actual column names. */ rte->eref = makeAlias(refname, NIL); - buildRelationAliases(rel->rd_att, alias, rte->eref); + buildRelationAliases(rel->rd_att, alias, rte->eref, false); /* * Set flags and access permissions. @@ -1235,17 +1274,23 @@ addRangeTableEntryForFunction(ParseState *pstate, /* Composite data type, e.g. a table's row type */ Assert(tupdesc); /* Build the column alias list */ - buildRelationAliases(tupdesc, alias, eref); + buildRelationAliases(tupdesc, alias, eref, rangefunc->ordinality); } else if (functypclass == TYPEFUNC_SCALAR) { /* Base data type, i.e. scalar */ - buildScalarFunctionAlias(funcexpr, funcname, alias, eref); + 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. @@ -1288,6 +1333,7 @@ addRangeTableEntryForFunction(ParseState *pstate, * permissions mechanism). */ rte->lateral = lateral; + rte->funcordinality = rangefunc->ordinality; rte->inh = false; /* never true for functions */ rte->inFromCl = inFromCl; @@ -1643,6 +1689,11 @@ addRTEtoQuery(ParseState *pstate, RangeTblEntry *rte, * The output lists go into *colnames and *colvars. * If only one of the two kinds of output list is needed, pass NULL for the * output pointer for the unwanted one. + * + * For function RTEs with ORDINALITY, this expansion includes the + * ordinal column, whose type (bigint) had better match the type assumed in the + * executor. The colname for the ordinality column must have been set up already + * in the RTE; it is always last. */ void expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, @@ -1711,6 +1762,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, TypeFuncClass functypclass; Oid funcrettype; TupleDesc tupdesc; + int ordinality_attno = 0; functypclass = get_expr_result_type(rte->funcexpr, &funcrettype, @@ -1719,9 +1771,16 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, { /* 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) { @@ -1742,6 +1801,8 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, *colvars = lappend(*colvars, varnode); } + + ordinality_attno = 2; } else if (functypclass == TYPEFUNC_RECORD) { @@ -1774,12 +1835,34 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, *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); + } + } } break; case RTE_VALUES: @@ -1955,6 +2038,9 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up, /* * 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, @@ -2114,6 +2200,9 @@ get_rte_attribute_name(RangeTblEntry *rte, AttrNumber attnum) /* * get_rte_attribute_type * Get attribute type/typmod/collation information from a RangeTblEntry + * + * Once again, for function RTEs we may have to synthesize the + * ordinality column with the correct type. */ void get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, @@ -2172,6 +2261,20 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, 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)) + { + *vartype = INT8OID; + *vartypmod = -1; + *varcollid = InvalidOid; + break; + } + functypclass = get_expr_result_type(rte->funcexpr, &funcrettype, &tupdesc); @@ -2182,6 +2285,7 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, Form_pg_attribute att_tup; Assert(tupdesc); + /* this is probably a can't-happen case */ if (attnum < 1 || attnum > tupdesc->natts) ereport(ERROR, @@ -2208,6 +2312,8 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, } else if (functypclass == TYPEFUNC_SCALAR) { + Assert(attnum == 1); + /* Base data type, i.e. scalar */ *vartype = funcrettype; *vartypmod = -1; @@ -2332,7 +2438,17 @@ get_rte_attribute_is_dropped(RangeTblEntry *rte, AttrNumber attnum) Oid funcrettype = exprType(rte->funcexpr); Oid funcrelid = typeidTypeRelid(funcrettype); - if (OidIsValid(funcrelid)) + /* + * 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 diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index b8ec790466..541d3643a1 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -133,7 +133,7 @@ base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t yyscanner) case WITH: /* - * WITH TIME must be reduced to one token + * WITH TIME and WITH ORDINALITY must each be reduced to one token */ cur_yylval = lvalp->core_yystype; cur_yylloc = *llocp; @@ -143,6 +143,9 @@ base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t yyscanner) case TIME: cur_token = WITH_TIME; break; + case ORDINALITY: + cur_token = WITH_ORDINALITY; + break; default: /* save the lookahead token for next time */ yyextra->lookahead_token = next_token; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index e6a20e3821..2b005d6e97 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -8004,6 +8004,8 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context) case RTE_FUNCTION: /* Function RTE */ get_rule_expr(rte->funcexpr, context, true); + if (rte->funcordinality) + appendStringInfoString(buf, " WITH ORDINALITY"); break; case RTE_VALUES: /* Values list RTE */ diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 51c5575f07..49226b70e6 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -87,6 +87,7 @@ extern TupleDesc CreateTupleDesc(int natts, bool hasoid, Form_pg_attribute *attrs); extern TupleDesc CreateTupleDescCopy(TupleDesc tupdesc); +extern TupleDesc CreateTupleDescCopyExtend(TupleDesc tupdesc, int moreatts); extern TupleDesc CreateTupleDescCopyConstr(TupleDesc tupdesc); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 298af26e96..3b430e0f24 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1395,7 +1395,10 @@ typedef struct SubqueryScanState * function appearing in FROM (typically a function returning set). * * eflags node's capability flags - * tupdesc expected return tuple description + * 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 * ---------------- @@ -1404,7 +1407,10 @@ typedef struct FunctionScanState { ScanState ss; /* its first field is NodeTag */ int eflags; - TupleDesc tupdesc; + int64 ordinal; + TupleDesc scan_tupdesc; + TupleDesc func_tupdesc; + TupleTableSlot *func_slot; Tuplestorestate *tuplestorestate; ExprState *funcexpr; } FunctionScanState; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b4013e893d..51fef68ca3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -471,6 +471,7 @@ 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 @@ -651,8 +652,13 @@ typedef struct XmlSerialize * dropped columns. Note however that a stored rule may have nonempty * 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. + * 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 @@ -751,15 +757,21 @@ typedef struct RangeTblEntry /* * Fields valid for a function RTE (else NULL): * - * If the function returns RECORD, funccoltypes lists the column types - * declared in the RTE's column type specification, funccoltypmods lists - * their declared typmods, funccolcollations their collations. Otherwise, - * those fields are NIL. + * 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): diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index aa4f12cec5..44ea0b7752 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -425,6 +425,7 @@ 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 */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 287f78e72a..8bd34d6e8f 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -269,6 +269,7 @@ PG_KEYWORD("option", OPTION, UNRESERVED_KEYWORD) PG_KEYWORD("options", OPTIONS, UNRESERVED_KEYWORD) PG_KEYWORD("or", OR, RESERVED_KEYWORD) PG_KEYWORD("order", ORDER, RESERVED_KEYWORD) +PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD) PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD) PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD) diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 16782776f4..45ffd85b1b 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -18,7 +18,131 @@ CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); INSERT INTO foo2 VALUES(2, 22); INSERT INTO foo2 VALUES(1, 111); -CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; +CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1 ORDER BY f2;' LANGUAGE SQL; +-- function with ORDINALITY +select * from foot(1) with ordinality as z(a,b,ord); + a | b | ord +---+-----+----- + 1 | 11 | 1 + 1 | 111 | 2 +(2 rows) + +select * from foot(1) with ordinality as z(a,b,ord) where b > 100; -- ordinal 2, not 1 + a | b | ord +---+-----+----- + 1 | 111 | 2 +(1 row) + +-- ordinality vs. column names and types +select a,b,ord from foot(1) with ordinality as z(a,b,ord); + a | b | ord +---+-----+----- + 1 | 11 | 1 + 1 | 111 | 2 +(2 rows) + +select a,ord from unnest(array['a','b']) with ordinality as z(a,ord); + a | ord +---+----- + a | 1 + b | 2 +(2 rows) + +select * from unnest(array['a','b']) with ordinality as z(a,ord); + a | ord +---+----- + a | 1 + b | 2 +(2 rows) + +select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord); + a | ord +---+----- + 1 | 1 +(1 row) + +select * from unnest(array[1.0::float8]) with ordinality as z(a,ord); + a | ord +---+----- + 1 | 1 +(1 row) + +-- ordinality vs. views +create temporary view vw_ord as select * from (values (1)) v(n) join foot(1) with ordinality as z(a,b,ord) on (n=ord); +select * from vw_ord; + n | a | b | ord +---+---+----+----- + 1 | 1 | 11 | 1 +(1 row) + +select definition from pg_views where viewname='vw_ord'; + definition +------------------------------------------------------------------- + SELECT v.n, + + z.a, + + z.b, + + z.ord + + FROM (( VALUES (1)) v(n) + + JOIN foot(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord))); +(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; -- function with implicit LATERAL select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; fooid | f2 | fooid | f2 @@ -28,6 +152,15 @@ select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; 1 | 111 | 1 | 111 (3 rows) +-- function with implicit LATERAL and explicit ORDINALITY +select * from foo2, foot(foo2.fooid) with ordinality as z(fooid,f2,ord) where foo2.f2 = z.f2; + fooid | f2 | fooid | f2 | ord +-------+-----+-------+-----+----- + 1 | 11 | 1 | 11 | 1 + 2 | 22 | 2 | 22 | 1 + 1 | 111 | 1 | 111 | 2 +(3 rows) + -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2; fooid | f2 @@ -73,6 +206,12 @@ SELECT * FROM getfoo(1) AS 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 @@ -80,6 +219,14 @@ SELECT * FROM vw_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); @@ -91,6 +238,13 @@ SELECT * FROM getfoo(1) AS t1; 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 @@ -99,6 +253,15 @@ SELECT * FROM vw_getfoo; 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 +---+--- + 1 | 1 + 1 | 2 +(2 rows) + -- sql, proretset = t, prorettype = b DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); @@ -110,6 +273,13 @@ SELECT * FROM getfoo(1) AS t1; 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 @@ -118,6 +288,15 @@ SELECT * FROM vw_getfoo; 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 +-----+--- + Joe | 1 + Ed | 2 +(2 rows) + -- sql, proretset = f, prorettype = c DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); @@ -128,6 +307,12 @@ SELECT * FROM getfoo(1) AS t1; 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 @@ -135,6 +320,14 @@ SELECT * FROM vw_getfoo; 1 | 1 | Joe (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); @@ -146,6 +339,13 @@ SELECT * FROM getfoo(1) AS t1; 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 @@ -154,6 +354,16 @@ SELECT * FROM vw_getfoo; 1 | 2 | Ed (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 +---+---+-----+--- + 1 | 1 | Joe | 1 + 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); @@ -202,6 +412,12 @@ SELECT * FROM getfoo(1) AS 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 @@ -209,6 +425,14 @@ SELECT * FROM vw_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); @@ -219,6 +443,12 @@ SELECT * FROM getfoo(1) AS t1; 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 @@ -226,182 +456,623 @@ SELECT * FROM vw_getfoo; 1 | 1 | Joe (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) + DROP VIEW vw_getfoo; DROP FUNCTION getfoo(int); DROP FUNCTION foot(int); DROP TABLE foo2; DROP TABLE foo; -- Rescan tests -- -CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); -INSERT INTO foorescan values(5000,1,'abc.5000.1'); -INSERT INTO foorescan values(5001,1,'abc.5001.1'); -INSERT INTO foorescan values(5002,1,'abc.5002.1'); -INSERT INTO foorescan values(5003,1,'abc.5003.1'); -INSERT INTO foorescan values(5004,1,'abc.5004.1'); -INSERT INTO foorescan values(5005,1,'abc.5005.1'); -INSERT INTO foorescan values(5006,1,'abc.5006.1'); -INSERT INTO foorescan values(5007,1,'abc.5007.1'); -INSERT INTO foorescan values(5008,1,'abc.5008.1'); -INSERT INTO foorescan values(5009,1,'abc.5009.1'); -INSERT INTO foorescan values(5000,2,'abc.5000.2'); -INSERT INTO foorescan values(5001,2,'abc.5001.2'); -INSERT INTO foorescan values(5002,2,'abc.5002.2'); -INSERT INTO foorescan values(5003,2,'abc.5003.2'); -INSERT INTO foorescan values(5004,2,'abc.5004.2'); -INSERT INTO foorescan values(5005,2,'abc.5005.2'); -INSERT INTO foorescan values(5006,2,'abc.5006.2'); -INSERT INTO foorescan values(5007,2,'abc.5007.2'); -INSERT INTO foorescan values(5008,2,'abc.5008.2'); -INSERT INTO foorescan values(5009,2,'abc.5009.2'); -INSERT INTO foorescan values(5000,3,'abc.5000.3'); -INSERT INTO foorescan values(5001,3,'abc.5001.3'); -INSERT INTO foorescan values(5002,3,'abc.5002.3'); -INSERT INTO foorescan values(5003,3,'abc.5003.3'); -INSERT INTO foorescan values(5004,3,'abc.5004.3'); -INSERT INTO foorescan values(5005,3,'abc.5005.3'); -INSERT INTO foorescan values(5006,3,'abc.5006.3'); -INSERT INTO foorescan values(5007,3,'abc.5007.3'); -INSERT INTO foorescan values(5008,3,'abc.5008.3'); -INSERT INTO foorescan values(5009,3,'abc.5009.3'); -INSERT INTO foorescan values(5000,4,'abc.5000.4'); -INSERT INTO foorescan values(5001,4,'abc.5001.4'); -INSERT INTO foorescan values(5002,4,'abc.5002.4'); -INSERT INTO foorescan values(5003,4,'abc.5003.4'); -INSERT INTO foorescan values(5004,4,'abc.5004.4'); -INSERT INTO foorescan values(5005,4,'abc.5005.4'); -INSERT INTO foorescan values(5006,4,'abc.5006.4'); -INSERT INTO foorescan values(5007,4,'abc.5007.4'); -INSERT INTO foorescan values(5008,4,'abc.5008.4'); -INSERT INTO foorescan values(5009,4,'abc.5009.4'); -INSERT INTO foorescan values(5000,5,'abc.5000.5'); -INSERT INTO foorescan values(5001,5,'abc.5001.5'); -INSERT INTO foorescan values(5002,5,'abc.5002.5'); -INSERT INTO foorescan values(5003,5,'abc.5003.5'); -INSERT INTO foorescan values(5004,5,'abc.5004.5'); -INSERT INTO foorescan values(5005,5,'abc.5005.5'); -INSERT INTO foorescan values(5006,5,'abc.5006.5'); -INSERT INTO foorescan values(5007,5,'abc.5007.5'); -INSERT INTO foorescan values(5008,5,'abc.5008.5'); -INSERT INTO foorescan values(5009,5,'abc.5009.5'); -CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL; ---invokes ExecReScanFunctionScan -SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2; - fooid | foosubid | fooname --------+----------+------------ - 5002 | 1 | abc.5002.1 - 5002 | 2 | abc.5002.2 - 5002 | 3 | abc.5002.3 - 5002 | 4 | abc.5002.4 - 5002 | 5 | abc.5002.5 - 5003 | 1 | abc.5003.1 - 5003 | 2 | abc.5003.2 - 5003 | 3 | abc.5003.3 - 5003 | 4 | abc.5003.4 - 5003 | 5 | abc.5003.5 +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; + r | i | s +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 2 | 11 | 1 + 2 | 12 | 2 + 2 | 13 | 3 + 3 | 11 | 1 + 3 | 12 | 2 + 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; + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 1 | 12 | 2 | 2 + 1 | 13 | 3 | 3 + 2 | 11 | 1 | 1 + 2 | 12 | 2 | 2 + 2 | 13 | 3 | 3 + 3 | 11 | 1 | 1 + 3 | 12 | 2 | 2 + 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; + r | i | s +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 2 | 11 | 1 + 2 | 12 | 2 + 2 | 13 | 3 + 3 | 11 | 1 + 3 | 12 | 2 + 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; + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 1 | 12 | 2 | 2 + 1 | 13 | 3 | 3 + 2 | 11 | 1 | 1 + 2 | 12 | 2 | 2 + 2 | 13 | 3 | 3 + 3 | 11 | 1 | 1 + 3 | 12 | 2 | 2 + 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 +---+---- + 1 | 11 + 1 | 12 + 1 | 13 + 2 | 11 + 2 | 12 + 2 | 13 + 3 | 11 + 3 | 12 + 3 | 13 +(9 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100; + r | i | o +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 2 | 11 | 1 + 2 | 12 | 2 + 2 | 13 | 3 + 3 | 11 | 1 + 3 | 12 | 2 + 3 | 13 | 3 +(9 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100; + r | i +---+---- + 1 | 10 + 1 | 20 + 1 | 30 + 2 | 10 + 2 | 20 + 2 | 30 + 3 | 10 + 3 | 20 + 3 | 30 +(9 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100; + r | i | o +---+----+--- + 1 | 10 | 1 + 1 | 20 | 2 + 1 | 30 | 3 + 2 | 10 | 1 + 2 | 20 | 2 + 2 | 30 | 3 + 3 | 10 | 1 + 3 | 20 | 2 + 3 | 30 | 3 +(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); + r | i | s +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 2 | 12 | 4 + 2 | 13 | 5 + 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); + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 1 | 12 | 2 | 2 + 1 | 13 | 3 | 3 + 2 | 12 | 4 | 1 + 2 | 13 | 5 | 2 + 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); + r | i | s +---+----+--- + 1 | 11 | 1 + 2 | 11 | 2 + 2 | 12 | 3 + 3 | 11 | 4 + 3 | 12 | 5 + 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); + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 2 | 11 | 2 | 1 + 2 | 12 | 3 | 2 + 3 | 11 | 4 | 1 + 3 | 12 | 5 | 2 + 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); + r1 | r2 | i | s +----+----+----+---- + 11 | 12 | 11 | 1 + 11 | 12 | 12 | 2 + 13 | 15 | 13 | 3 + 13 | 15 | 14 | 4 + 13 | 15 | 15 | 5 + 16 | 20 | 16 | 6 + 16 | 20 | 17 | 7 + 16 | 20 | 18 | 8 + 16 | 20 | 19 | 9 + 16 | 20 | 20 | 10 (10 rows) -CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); ---invokes ExecReScanFunctionScan -SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2; - fooid | foosubid | fooname --------+----------+------------ - 5002 | 1 | abc.5002.1 - 5002 | 2 | abc.5002.2 - 5002 | 3 | abc.5002.3 - 5002 | 4 | abc.5002.4 - 5002 | 5 | abc.5002.5 - 5003 | 1 | abc.5003.1 - 5003 | 2 | abc.5003.2 - 5003 | 3 | abc.5003.3 - 5003 | 4 | abc.5003.4 - 5003 | 5 | abc.5003.5 +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); + r1 | r2 | i | s | o +----+----+----+----+--- + 11 | 12 | 11 | 1 | 1 + 11 | 12 | 12 | 2 | 2 + 13 | 15 | 13 | 3 | 1 + 13 | 15 | 14 | 4 | 2 + 13 | 15 | 15 | 5 | 3 + 16 | 20 | 16 | 6 | 1 + 16 | 20 | 17 | 7 | 2 + 16 | 20 | 18 | 8 | 3 + 16 | 20 | 19 | 9 | 4 + 16 | 20 | 20 | 10 | 5 (10 rows) -CREATE TABLE barrescan (fooid int primary key); -INSERT INTO barrescan values(5003); -INSERT INTO barrescan values(5004); -INSERT INTO barrescan values(5005); -INSERT INTO barrescan values(5006); -INSERT INTO barrescan values(5007); -INSERT INTO barrescan values(5008); -CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL; ---invokes ExecReScanFunctionScan with chgParam != NULL -SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; - fooid | foosubid | fooname --------+----------+------------ - 5003 | 1 | abc.5003.1 - 5003 | 2 | abc.5003.2 - 5003 | 3 | abc.5003.3 - 5003 | 4 | abc.5003.4 - 5003 | 5 | abc.5003.5 - 5004 | 1 | abc.5004.1 - 5004 | 2 | abc.5004.2 - 5004 | 3 | abc.5004.3 - 5004 | 4 | abc.5004.4 - 5004 | 5 | abc.5004.5 - 5005 | 1 | abc.5005.1 - 5005 | 2 | abc.5005.2 - 5005 | 3 | abc.5005.3 - 5005 | 4 | abc.5005.4 - 5005 | 5 | abc.5005.5 - 5006 | 1 | abc.5006.1 - 5006 | 2 | abc.5006.2 - 5006 | 3 | abc.5006.3 - 5006 | 4 | abc.5006.4 - 5006 | 5 | abc.5006.5 - 5007 | 1 | abc.5007.1 - 5007 | 2 | abc.5007.2 - 5007 | 3 | abc.5007.3 - 5007 | 4 | abc.5007.4 - 5007 | 5 | abc.5007.5 - 5008 | 1 | abc.5008.1 - 5008 | 2 | abc.5008.2 - 5008 | 3 | abc.5008.3 - 5008 | 4 | abc.5008.4 - 5008 | 5 | abc.5008.5 -(30 rows) - -SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; - fooid | max --------+----- - 5003 | 5 - 5004 | 5 - 5005 | 5 - 5006 | 5 - 5007 | 5 - 5008 | 5 +SELECT setval('foo_rescan_seq',1,false); + setval +-------- + 1 +(1 row) + +SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13); + r | i | s +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 2 | 12 | 4 + 2 | 13 | 5 + 3 | 13 | 6 (6 rows) -CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; -SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; - fooid | foosubid | fooname --------+----------+------------ - 5004 | 1 | abc.5004.1 - 5004 | 2 | abc.5004.2 - 5004 | 3 | abc.5004.3 - 5004 | 4 | abc.5004.4 - 5004 | 5 | abc.5004.5 -(5 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); + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 1 | 12 | 2 | 2 + 1 | 13 | 3 | 3 + 2 | 12 | 4 | 1 + 2 | 13 | 5 | 2 + 3 | 13 | 6 | 1 +(6 rows) + +SELECT setval('foo_rescan_seq',1,false); + setval +-------- + 1 +(1 row) -CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; -SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; - fooid | maxsubid --------+---------- - 5003 | 5 - 5004 | 5 - 5005 | 5 - 5006 | 5 - 5007 | 5 - 5008 | 5 +SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r); + r | i | s +---+----+--- + 1 | 11 | 1 + 2 | 11 | 2 + 2 | 12 | 3 + 3 | 11 | 4 + 3 | 12 | 5 + 3 | 13 | 6 (6 rows) -DROP VIEW vw_foorescan; -DROP VIEW fooview1; -DROP VIEW fooview2; -DROP FUNCTION foorescan(int,int); -DROP FUNCTION foorescan(int); -DROP TABLE foorescan; -DROP TABLE barrescan; +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); + r | i | s | o +---+----+---+--- + 1 | 11 | 1 | 1 + 2 | 11 | 2 | 1 + 2 | 12 | 3 | 2 + 3 | 11 | 4 | 1 + 3 | 12 | 5 | 2 + 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); + r1 | r2 | i | s +----+----+----+---- + 11 | 12 | 11 | 1 + 11 | 12 | 12 | 2 + 13 | 15 | 13 | 3 + 13 | 15 | 14 | 4 + 13 | 15 | 15 | 5 + 16 | 20 | 16 | 6 + 16 | 20 | 17 | 7 + 16 | 20 | 18 | 8 + 16 | 20 | 19 | 9 + 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); + r1 | r2 | i | s | o +----+----+----+----+--- + 11 | 12 | 11 | 1 | 1 + 11 | 12 | 12 | 2 | 2 + 13 | 15 | 13 | 3 | 1 + 13 | 15 | 14 | 4 | 2 + 13 | 15 | 15 | 5 | 3 + 16 | 20 | 16 | 6 | 1 + 16 | 20 | 17 | 7 | 2 + 16 | 20 | 18 | 8 | 3 + 16 | 20 | 19 | 9 | 4 + 16 | 20 | 20 | 10 | 5 +(10 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i); + r | i +---+---- + 1 | 11 + 1 | 12 + 1 | 13 + 1 | 14 + 1 | 15 + 1 | 16 + 1 | 17 + 1 | 18 + 1 | 19 + 2 | 12 + 2 | 13 + 2 | 14 + 2 | 15 + 2 | 16 + 2 | 17 + 2 | 18 + 3 | 13 + 3 | 14 + 3 | 15 + 3 | 16 + 3 | 17 +(21 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o); + r | i | o +---+----+--- + 1 | 11 | 1 + 1 | 12 | 2 + 1 | 13 | 3 + 1 | 14 | 4 + 1 | 15 | 5 + 1 | 16 | 6 + 1 | 17 | 7 + 1 | 18 | 8 + 1 | 19 | 9 + 2 | 12 | 1 + 2 | 13 | 2 + 2 | 14 | 3 + 2 | 15 | 4 + 2 | 16 | 5 + 2 | 17 | 6 + 2 | 18 | 7 + 3 | 13 | 1 + 3 | 14 | 2 + 3 | 15 | 3 + 3 | 16 | 4 + 3 | 17 | 5 +(21 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i); + r | i +---+---- + 1 | 10 + 1 | 20 + 1 | 30 + 2 | 20 + 2 | 40 + 2 | 60 + 3 | 30 + 3 | 60 + 3 | 90 +(9 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o); + r | i | o +---+----+--- + 1 | 10 | 1 + 1 | 20 | 2 + 1 | 30 | 3 + 2 | 20 | 1 + 2 | 40 | 2 + 2 | 60 | 3 + 3 | 30 | 1 + 3 | 60 | 2 + 3 | 90 | 3 +(9 rows) + +-- deep nesting +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; + r1 | r1 | r2 | i +----+----+----+---- + 1 | 1 | 10 | 21 + 1 | 1 | 10 | 22 + 1 | 1 | 10 | 23 + 1 | 1 | 20 | 21 + 1 | 1 | 20 | 22 + 1 | 1 | 20 | 23 + 1 | 1 | 30 | 21 + 1 | 1 | 30 | 22 + 1 | 1 | 30 | 23 + 2 | 2 | 10 | 21 + 2 | 2 | 10 | 22 + 2 | 2 | 10 | 23 + 2 | 2 | 20 | 21 + 2 | 2 | 20 | 22 + 2 | 2 | 20 | 23 + 2 | 2 | 30 | 21 + 2 | 2 | 30 | 22 + 2 | 2 | 30 | 23 + 3 | 3 | 10 | 21 + 3 | 3 | 10 | 22 + 3 | 3 | 10 | 23 + 3 | 3 | 20 | 21 + 3 | 3 | 20 | 22 + 3 | 3 | 20 | 23 + 3 | 3 | 30 | 21 + 3 | 3 | 30 | 22 + 3 | 3 | 30 | 23 +(27 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; + r1 | r1 | r2 | i +----+----+----+---- + 1 | 1 | 10 | 21 + 1 | 1 | 10 | 22 + 1 | 1 | 10 | 23 + 1 | 1 | 20 | 21 + 1 | 1 | 20 | 22 + 1 | 1 | 20 | 23 + 1 | 1 | 30 | 21 + 1 | 1 | 30 | 22 + 1 | 1 | 30 | 23 + 2 | 2 | 10 | 22 + 2 | 2 | 10 | 23 + 2 | 2 | 20 | 22 + 2 | 2 | 20 | 23 + 2 | 2 | 30 | 22 + 2 | 2 | 30 | 23 + 3 | 3 | 10 | 23 + 3 | 3 | 20 | 23 + 3 | 3 | 30 | 23 +(18 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1; + r1 | r1 | r2 | i +----+----+----+---- + 1 | 1 | 10 | 10 + 1 | 1 | 10 | 11 + 1 | 1 | 10 | 12 + 1 | 1 | 10 | 13 + 1 | 1 | 20 | 20 + 1 | 1 | 20 | 21 + 1 | 1 | 20 | 22 + 1 | 1 | 20 | 23 + 1 | 1 | 30 | 30 + 1 | 1 | 30 | 31 + 1 | 1 | 30 | 32 + 1 | 1 | 30 | 33 + 2 | 2 | 10 | 10 + 2 | 2 | 10 | 11 + 2 | 2 | 10 | 12 + 2 | 2 | 10 | 13 + 2 | 2 | 20 | 20 + 2 | 2 | 20 | 21 + 2 | 2 | 20 | 22 + 2 | 2 | 20 | 23 + 2 | 2 | 30 | 30 + 2 | 2 | 30 | 31 + 2 | 2 | 30 | 32 + 2 | 2 | 30 | 33 + 3 | 3 | 10 | 10 + 3 | 3 | 10 | 11 + 3 | 3 | 10 | 12 + 3 | 3 | 10 | 13 + 3 | 3 | 20 | 20 + 3 | 3 | 20 | 21 + 3 | 3 | 20 | 22 + 3 | 3 | 20 | 23 + 3 | 3 | 30 | 30 + 3 | 3 | 30 | 31 + 3 | 3 | 30 | 32 + 3 | 3 | 30 | 33 +(36 rows) + +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1; + r1 | r1 | r2 | i +----+----+----+--- + 1 | 1 | 10 | 1 + 1 | 1 | 10 | 2 + 1 | 1 | 10 | 3 + 1 | 1 | 10 | 4 + 1 | 1 | 20 | 1 + 1 | 1 | 20 | 2 + 1 | 1 | 20 | 3 + 1 | 1 | 20 | 4 + 1 | 1 | 20 | 5 + 1 | 1 | 20 | 6 + 1 | 1 | 30 | 1 + 1 | 1 | 30 | 2 + 1 | 1 | 30 | 3 + 1 | 1 | 30 | 4 + 1 | 1 | 30 | 5 + 1 | 1 | 30 | 6 + 1 | 1 | 30 | 7 + 1 | 1 | 30 | 8 + 2 | 2 | 10 | 2 + 2 | 2 | 10 | 3 + 2 | 2 | 10 | 4 + 2 | 2 | 20 | 2 + 2 | 2 | 20 | 3 + 2 | 2 | 20 | 4 + 2 | 2 | 20 | 5 + 2 | 2 | 20 | 6 + 2 | 2 | 30 | 2 + 2 | 2 | 30 | 3 + 2 | 2 | 30 | 4 + 2 | 2 | 30 | 5 + 2 | 2 | 30 | 6 + 2 | 2 | 30 | 7 + 2 | 2 | 30 | 8 + 3 | 3 | 10 | 3 + 3 | 3 | 10 | 4 + 3 | 3 | 20 | 3 + 3 | 3 | 20 | 4 + 3 | 3 | 20 | 5 + 3 | 3 | 20 | 6 + 3 | 3 | 30 | 3 + 3 | 3 | 30 | 4 + 3 | 3 | 30 | 5 + 3 | 3 | 30 | 6 + 3 | 3 | 30 | 7 + 3 | 3 | 30 | 8 +(45 rows) + +DROP FUNCTION foo_sql(int,int); +DROP FUNCTION foo_mat(int,int); +DROP SEQUENCE foo_rescan_seq; -- -- Test cases involving OUT parameters -- @@ -877,6 +1548,13 @@ SELECT * FROM get_users(); id2 | email2 | t (2 rows) +SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes + userid | email | enabled | ordinality +--------+--------+---------+------------ + id | email | t | 1 + id2 | email2 | t | 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 f1a405a5f7..d522cdb10e 100644 --- a/src/test/regress/sql/rangefuncs.sql +++ b/src/test/regress/sql/rangefuncs.sql @@ -5,11 +5,40 @@ INSERT INTO foo2 VALUES(1, 11); INSERT INTO foo2 VALUES(2, 22); INSERT INTO foo2 VALUES(1, 111); -CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; +CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1 ORDER BY f2;' LANGUAGE SQL; + +-- function with ORDINALITY +select * from foot(1) with ordinality as z(a,b,ord); +select * from foot(1) with ordinality as z(a,b,ord) where b > 100; -- ordinal 2, not 1 +-- ordinality vs. column names and types +select a,b,ord from foot(1) with ordinality as z(a,b,ord); +select a,ord from unnest(array['a','b']) with ordinality as z(a,ord); +select * from unnest(array['a','b']) with ordinality as z(a,ord); +select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord); +select * from unnest(array[1.0::float8]) with ordinality as z(a,ord); +-- ordinality vs. views +create temporary view vw_ord as select * from (values (1)) v(n) join foot(1) with ordinality as z(a,b,ord) on (n=ord); +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; +fetch next from foo; +fetch next from foo; +fetch prior from foo; +fetch absolute 1 from foo; +commit; -- function with implicit LATERAL select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2; +-- function with implicit LATERAL and explicit ORDINALITY +select * from foo2, foot(foo2.fooid) with ordinality as z(fooid,f2,ord) where foo2.f2 = z.f2; + -- function in subselect select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2; @@ -30,41 +59,62 @@ 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); @@ -88,16 +138,24 @@ 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); @@ -106,99 +164,85 @@ DROP TABLE foo2; DROP TABLE foo; -- Rescan tests -- -CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid)); -INSERT INTO foorescan values(5000,1,'abc.5000.1'); -INSERT INTO foorescan values(5001,1,'abc.5001.1'); -INSERT INTO foorescan values(5002,1,'abc.5002.1'); -INSERT INTO foorescan values(5003,1,'abc.5003.1'); -INSERT INTO foorescan values(5004,1,'abc.5004.1'); -INSERT INTO foorescan values(5005,1,'abc.5005.1'); -INSERT INTO foorescan values(5006,1,'abc.5006.1'); -INSERT INTO foorescan values(5007,1,'abc.5007.1'); -INSERT INTO foorescan values(5008,1,'abc.5008.1'); -INSERT INTO foorescan values(5009,1,'abc.5009.1'); - -INSERT INTO foorescan values(5000,2,'abc.5000.2'); -INSERT INTO foorescan values(5001,2,'abc.5001.2'); -INSERT INTO foorescan values(5002,2,'abc.5002.2'); -INSERT INTO foorescan values(5003,2,'abc.5003.2'); -INSERT INTO foorescan values(5004,2,'abc.5004.2'); -INSERT INTO foorescan values(5005,2,'abc.5005.2'); -INSERT INTO foorescan values(5006,2,'abc.5006.2'); -INSERT INTO foorescan values(5007,2,'abc.5007.2'); -INSERT INTO foorescan values(5008,2,'abc.5008.2'); -INSERT INTO foorescan values(5009,2,'abc.5009.2'); - -INSERT INTO foorescan values(5000,3,'abc.5000.3'); -INSERT INTO foorescan values(5001,3,'abc.5001.3'); -INSERT INTO foorescan values(5002,3,'abc.5002.3'); -INSERT INTO foorescan values(5003,3,'abc.5003.3'); -INSERT INTO foorescan values(5004,3,'abc.5004.3'); -INSERT INTO foorescan values(5005,3,'abc.5005.3'); -INSERT INTO foorescan values(5006,3,'abc.5006.3'); -INSERT INTO foorescan values(5007,3,'abc.5007.3'); -INSERT INTO foorescan values(5008,3,'abc.5008.3'); -INSERT INTO foorescan values(5009,3,'abc.5009.3'); - -INSERT INTO foorescan values(5000,4,'abc.5000.4'); -INSERT INTO foorescan values(5001,4,'abc.5001.4'); -INSERT INTO foorescan values(5002,4,'abc.5002.4'); -INSERT INTO foorescan values(5003,4,'abc.5003.4'); -INSERT INTO foorescan values(5004,4,'abc.5004.4'); -INSERT INTO foorescan values(5005,4,'abc.5005.4'); -INSERT INTO foorescan values(5006,4,'abc.5006.4'); -INSERT INTO foorescan values(5007,4,'abc.5007.4'); -INSERT INTO foorescan values(5008,4,'abc.5008.4'); -INSERT INTO foorescan values(5009,4,'abc.5009.4'); - -INSERT INTO foorescan values(5000,5,'abc.5000.5'); -INSERT INTO foorescan values(5001,5,'abc.5001.5'); -INSERT INTO foorescan values(5002,5,'abc.5002.5'); -INSERT INTO foorescan values(5003,5,'abc.5003.5'); -INSERT INTO foorescan values(5004,5,'abc.5004.5'); -INSERT INTO foorescan values(5005,5,'abc.5005.5'); -INSERT INTO foorescan values(5006,5,'abc.5006.5'); -INSERT INTO foorescan values(5007,5,'abc.5007.5'); -INSERT INTO foorescan values(5008,5,'abc.5008.5'); -INSERT INTO foorescan values(5009,5,'abc.5009.5'); - -CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL; - ---invokes ExecReScanFunctionScan -SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2; - -CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004); - ---invokes ExecReScanFunctionScan -SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2; - -CREATE TABLE barrescan (fooid int primary key); -INSERT INTO barrescan values(5003); -INSERT INTO barrescan values(5004); -INSERT INTO barrescan values(5005); -INSERT INTO barrescan values(5006); -INSERT INTO barrescan values(5007); -INSERT INTO barrescan values(5008); - -CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL; - ---invokes ExecReScanFunctionScan with chgParam != NULL -SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; -SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; - -CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2; -SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004; - -CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; -SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5; - -DROP VIEW vw_foorescan; -DROP VIEW fooview1; -DROP VIEW fooview2; -DROP FUNCTION foorescan(int,int); -DROP FUNCTION foorescan(int); -DROP TABLE foorescan; -DROP TABLE barrescan; +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; + +SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100; +SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100; + +--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); + +SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i); +SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o); + +-- deep nesting + +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1; +SELECT * FROM (VALUES (1),(2),(3)) v1(r1), + LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) + LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1; + +DROP FUNCTION foo_sql(int,int); +DROP FUNCTION foo_mat(int,int); +DROP SEQUENCE foo_rescan_seq; -- -- Test cases involving OUT parameters @@ -414,6 +458,7 @@ language sql stable; SELECT get_users(); SELECT * FROM get_users(); +SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes drop function get_first_user(); drop function get_users(); -- 2.40.0