diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index cbcd6cf..98bcfa0 100644 *** a/src/backend/catalog/information_schema.sql --- b/src/backend/catalog/information_schema.sql *************** CREATE VIEW user_mapping_options AS *** 2936,2947 **** SELECT authorization_identifier, foreign_server_catalog, foreign_server_name, ! CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name, CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user) OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE')) ! OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value ELSE NULL END AS character_data) AS option_value ! FROM _pg_user_mappings um; GRANT SELECT ON user_mapping_options TO PUBLIC; --- 2936,2949 ---- SELECT authorization_identifier, foreign_server_catalog, foreign_server_name, ! CAST(opts.option_name AS sql_identifier) AS option_name, CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user) OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE')) ! OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) ! THEN opts.option_value ELSE NULL END AS character_data) AS option_value ! FROM _pg_user_mappings um, ! pg_options_to_table(um.umoptions) opts; GRANT SELECT ON user_mapping_options TO PUBLIC; diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index a35ba32..89aea2f 100644 *** a/src/backend/executor/functions.c --- b/src/backend/executor/functions.c *************** sql_fn_post_column_ref(ParseState *pstat *** 388,393 **** --- 388,394 ---- param = ParseFuncOrColumn(pstate, list_make1(subfield), list_make1(param), + pstate->p_last_srf, NULL, cref->location); } diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index efe1c37..5241fd2 100644 *** a/src/backend/parser/parse_agg.c --- b/src/backend/parser/parse_agg.c *************** check_agg_arguments_walker(Node *node, *** 705,710 **** --- 705,717 ---- } /* Continue and descend into subtree */ } + /* We can throw error on sight for a set-returning function */ + if ((IsA(node, FuncExpr) &&((FuncExpr *) node)->funcretset) || + (IsA(node, OpExpr) &&((OpExpr *) node)->opretset)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("aggregate function calls cannot contain set-returning function calls"), + parser_errposition(context->pstate, exprLocation(node)))); /* We can throw error on sight for a window function */ if (IsA(node, WindowFunc)) ereport(ERROR, diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 27dd49d..3d5b208 100644 *** a/src/backend/parser/parse_clause.c --- b/src/backend/parser/parse_clause.c *************** transformRangeFunction(ParseState *pstat *** 572,577 **** --- 572,579 ---- List *pair = (List *) lfirst(lc); Node *fexpr; List *coldeflist; + Node *newfexpr; + Node *last_srf; /* Disassemble the function-call/column-def-list pairs */ Assert(list_length(pair) == 2); *************** transformRangeFunction(ParseState *pstat *** 618,630 **** Node *arg = (Node *) lfirst(lc); FuncCall *newfc; newfc = makeFuncCall(SystemFuncName("unnest"), list_make1(arg), fc->location); ! funcexprs = lappend(funcexprs, ! transformExpr(pstate, (Node *) newfc, ! EXPR_KIND_FROM_FUNCTION)); funcnames = lappend(funcnames, FigureColname((Node *) newfc)); --- 620,644 ---- Node *arg = (Node *) lfirst(lc); FuncCall *newfc; + last_srf = pstate->p_last_srf; + newfc = makeFuncCall(SystemFuncName("unnest"), list_make1(arg), fc->location); ! newfexpr = transformExpr(pstate, (Node *) newfc, ! EXPR_KIND_FROM_FUNCTION); ! ! /* nodeFunctionscan.c requires SRFs to be at top level */ ! if (pstate->p_last_srf != last_srf && ! pstate->p_last_srf != newfexpr) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("set-returning functions must appear at top level of FROM"), ! parser_errposition(pstate, ! exprLocation(pstate->p_last_srf)))); ! ! funcexprs = lappend(funcexprs, newfexpr); funcnames = lappend(funcnames, FigureColname((Node *) newfc)); *************** transformRangeFunction(ParseState *pstat *** 638,646 **** } /* normal case ... */ ! funcexprs = lappend(funcexprs, ! transformExpr(pstate, fexpr, ! EXPR_KIND_FROM_FUNCTION)); funcnames = lappend(funcnames, FigureColname(fexpr)); --- 652,672 ---- } /* normal case ... */ ! last_srf = pstate->p_last_srf; ! ! newfexpr = transformExpr(pstate, fexpr, ! EXPR_KIND_FROM_FUNCTION); ! ! /* nodeFunctionscan.c requires SRFs to be at top level */ ! if (pstate->p_last_srf != last_srf && ! pstate->p_last_srf != newfexpr) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("set-returning functions must appear at top level of FROM"), ! parser_errposition(pstate, ! exprLocation(pstate->p_last_srf)))); ! ! funcexprs = lappend(funcexprs, newfexpr); funcnames = lappend(funcnames, FigureColname(fexpr)); diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 92101c9..da5fb7f 100644 *** a/src/backend/parser/parse_expr.c --- b/src/backend/parser/parse_expr.c *************** static Node *transformCurrentOfExpr(Pars *** 118,125 **** static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref); static Node *transformWholeRowRef(ParseState *pstate, RangeTblEntry *rte, int location); ! static Node *transformIndirection(ParseState *pstate, Node *basenode, ! List *indirection); static Node *transformTypeCast(ParseState *pstate, TypeCast *tc); static Node *transformCollateClause(ParseState *pstate, CollateClause *c); static Node *make_row_comparison_op(ParseState *pstate, List *opname, --- 118,124 ---- static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref); static Node *transformWholeRowRef(ParseState *pstate, RangeTblEntry *rte, int location); ! static Node *transformIndirection(ParseState *pstate, A_Indirection *ind); static Node *transformTypeCast(ParseState *pstate, TypeCast *tc); static Node *transformCollateClause(ParseState *pstate, CollateClause *c); static Node *make_row_comparison_op(ParseState *pstate, List *opname, *************** transformExprRecurse(ParseState *pstate, *** 192,205 **** } case T_A_Indirection: ! { ! A_Indirection *ind = (A_Indirection *) expr; ! ! result = transformExprRecurse(pstate, ind->arg); ! result = transformIndirection(pstate, result, ! ind->indirection); ! break; ! } case T_A_ArrayExpr: result = transformArrayExpr(pstate, (A_ArrayExpr *) expr, --- 191,198 ---- } case T_A_Indirection: ! result = transformIndirection(pstate, (A_Indirection *) expr); ! break; case T_A_ArrayExpr: result = transformArrayExpr(pstate, (A_ArrayExpr *) expr, *************** unknown_attribute(ParseState *pstate, No *** 439,449 **** } static Node * ! transformIndirection(ParseState *pstate, Node *basenode, List *indirection) { ! Node *result = basenode; List *subscripts = NIL; ! int location = exprLocation(basenode); ListCell *i; /* --- 432,443 ---- } static Node * ! transformIndirection(ParseState *pstate, A_Indirection *ind) { ! Node *last_srf = pstate->p_last_srf; ! Node *result = transformExprRecurse(pstate, ind->arg); List *subscripts = NIL; ! int location = exprLocation(result); ListCell *i; /* *************** transformIndirection(ParseState *pstate, *** 451,457 **** * subscripting. Adjacent A_Indices nodes have to be treated as a single * multidimensional subscript operation. */ ! foreach(i, indirection) { Node *n = lfirst(i); --- 445,451 ---- * subscripting. Adjacent A_Indices nodes have to be treated as a single * multidimensional subscript operation. */ ! foreach(i, ind->indirection) { Node *n = lfirst(i); *************** transformIndirection(ParseState *pstate, *** 484,489 **** --- 478,484 ---- newresult = ParseFuncOrColumn(pstate, list_make1(n), list_make1(result), + last_srf, NULL, location); if (newresult == NULL) *************** transformColumnRef(ParseState *pstate, C *** 632,637 **** --- 627,633 ---- node = ParseFuncOrColumn(pstate, list_make1(makeString(colname)), list_make1(node), + pstate->p_last_srf, NULL, cref->location); } *************** transformColumnRef(ParseState *pstate, C *** 678,683 **** --- 674,680 ---- node = ParseFuncOrColumn(pstate, list_make1(makeString(colname)), list_make1(node), + pstate->p_last_srf, NULL, cref->location); } *************** transformColumnRef(ParseState *pstate, C *** 737,742 **** --- 734,740 ---- node = ParseFuncOrColumn(pstate, list_make1(makeString(colname)), list_make1(node), + pstate->p_last_srf, NULL, cref->location); } *************** transformAExprOp(ParseState *pstate, A_E *** 927,932 **** --- 925,932 ---- else { /* Ordinary scalar operator */ + Node *last_srf = pstate->p_last_srf; + lexpr = transformExprRecurse(pstate, lexpr); rexpr = transformExprRecurse(pstate, rexpr); *************** transformAExprOp(ParseState *pstate, A_E *** 934,939 **** --- 934,940 ---- a->name, lexpr, rexpr, + last_srf, a->location); } *************** transformAExprNullIf(ParseState *pstate, *** 1053,1058 **** --- 1054,1060 ---- a->name, lexpr, rexpr, + pstate->p_last_srf, a->location); /* *************** transformAExprNullIf(ParseState *pstate, *** 1063,1068 **** --- 1065,1075 ---- (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("NULLIF requires = operator to yield boolean"), parser_errposition(pstate, a->location))); + if (result->opretset) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("NULLIF operator must not return a set"), + parser_errposition(pstate, a->location))); /* * ... but the NullIfExpr will yield the first operand's type. *************** transformAExprIn(ParseState *pstate, A_E *** 1266,1271 **** --- 1273,1279 ---- a->name, copyObject(lexpr), rexpr, + pstate->p_last_srf, a->location); } *************** transformBoolExpr(ParseState *pstate, Bo *** 1430,1435 **** --- 1438,1444 ---- static Node * transformFuncCall(ParseState *pstate, FuncCall *fn) { + Node *last_srf = pstate->p_last_srf; List *targs; ListCell *args; *************** transformFuncCall(ParseState *pstate, Fu *** 1465,1470 **** --- 1474,1480 ---- return ParseFuncOrColumn(pstate, fn->funcname, targs, + last_srf, fn, fn->location); } *************** transformMultiAssignRef(ParseState *psta *** 1619,1625 **** static Node * transformCaseExpr(ParseState *pstate, CaseExpr *c) { ! CaseExpr *newc; Node *arg; CaseTestExpr *placeholder; List *newargs; --- 1629,1636 ---- static Node * transformCaseExpr(ParseState *pstate, CaseExpr *c) { ! CaseExpr *newc = makeNode(CaseExpr); ! Node *last_srf = pstate->p_last_srf; Node *arg; CaseTestExpr *placeholder; List *newargs; *************** transformCaseExpr(ParseState *pstate, Ca *** 1628,1635 **** Node *defresult; Oid ptype; - newc = makeNode(CaseExpr); - /* transform the test expression, if any */ arg = transformExprRecurse(pstate, (Node *) c->arg); --- 1639,1644 ---- *************** transformCaseExpr(ParseState *pstate, Ca *** 1741,1746 **** --- 1750,1765 ---- "CASE/WHEN"); } + /* if any subexpression contained a SRF, complain */ + if (pstate->p_last_srf != last_srf) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + /* translator: %s is name of a SQL construct, eg GROUP BY */ + errmsg("set-returning functions are not allowed in %s", + "CASE"), + parser_errposition(pstate, + exprLocation(pstate->p_last_srf)))); + newc->location = c->location; return (Node *) newc; *************** static Node * *** 2177,2182 **** --- 2196,2202 ---- transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c) { CoalesceExpr *newc = makeNode(CoalesceExpr); + Node *last_srf = pstate->p_last_srf; List *newargs = NIL; List *newcoercedargs = NIL; ListCell *args; *************** transformCoalesceExpr(ParseState *pstate *** 2205,2210 **** --- 2225,2240 ---- newcoercedargs = lappend(newcoercedargs, newe); } + /* if any subexpression contained a SRF, complain */ + if (pstate->p_last_srf != last_srf) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + /* translator: %s is name of a SQL construct, eg GROUP BY */ + errmsg("set-returning functions are not allowed in %s", + "COALESCE"), + parser_errposition(pstate, + exprLocation(pstate->p_last_srf)))); + newc->args = newcoercedargs; newc->location = c->location; return (Node *) newc; *************** make_row_comparison_op(ParseState *pstat *** 2793,2799 **** Node *rarg = (Node *) lfirst(r); OpExpr *cmp; ! cmp = castNode(OpExpr, make_op(pstate, opname, larg, rarg, location)); /* * We don't use coerce_to_boolean here because we insist on the --- 2823,2830 ---- Node *rarg = (Node *) lfirst(r); OpExpr *cmp; ! cmp = castNode(OpExpr, make_op(pstate, opname, larg, rarg, ! pstate->p_last_srf, location)); /* * We don't use coerce_to_boolean here because we insist on the *************** make_distinct_op(ParseState *pstate, Lis *** 3000,3011 **** { Expr *result; ! result = make_op(pstate, opname, ltree, rtree, location); if (((OpExpr *) result)->opresulttype != BOOLOID) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("IS DISTINCT FROM requires = operator to yield boolean"), parser_errposition(pstate, location))); /* * We rely on DistinctExpr and OpExpr being same struct --- 3031,3048 ---- { Expr *result; ! result = make_op(pstate, opname, ltree, rtree, ! pstate->p_last_srf, location); if (((OpExpr *) result)->opresulttype != BOOLOID) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("IS DISTINCT FROM requires = operator to yield boolean"), parser_errposition(pstate, location))); + if (((OpExpr *) result)->opretset) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("IS DISTINCT FROM operator must not return a set"), + parser_errposition(pstate, location))); /* * We rely on DistinctExpr and OpExpr being same struct diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 55853c2..bb290c5 100644 *** a/src/backend/parser/parse_func.c --- b/src/backend/parser/parse_func.c *************** static Node *ParseComplexProjection(Pars *** 64,73 **** * * The argument expressions (in fargs) must have been transformed * already. However, nothing in *fn has been transformed. */ Node * ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, ! FuncCall *fn, int location) { bool is_column = (fn == NULL); List *agg_order = (fn ? fn->agg_order : NIL); --- 64,77 ---- * * The argument expressions (in fargs) must have been transformed * already. However, nothing in *fn has been transformed. + * + * last_srf should be a copy of pstate->p_last_srf from just before we + * started transforming fargs. If the caller knows that fargs couldn't + * contain any SRF calls, last_srf can just be pstate->p_last_srf. */ Node * ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, ! Node *last_srf, FuncCall *fn, int location) { bool is_column = (fn == NULL); List *agg_order = (fn ? fn->agg_order : NIL); *************** ParseFuncOrColumn(ParseState *pstate, Li *** 628,634 **** /* if it returns a set, check that's OK */ if (retset) ! check_srf_call_placement(pstate, location); /* build the appropriate output structure */ if (fdresult == FUNCDETAIL_NORMAL) --- 632,638 ---- /* if it returns a set, check that's OK */ if (retset) ! check_srf_call_placement(pstate, last_srf, location); /* build the appropriate output structure */ if (fdresult == FUNCDETAIL_NORMAL) *************** ParseFuncOrColumn(ParseState *pstate, Li *** 759,764 **** --- 763,778 ---- errmsg("FILTER is not implemented for non-aggregate window functions"), parser_errposition(pstate, location))); + /* + * Window functions can't either take or return sets + */ + if (pstate->p_last_srf != last_srf) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("window function calls cannot contain set-returning function calls"), + parser_errposition(pstate, + exprLocation(pstate->p_last_srf)))); + if (retset) ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), *************** ParseFuncOrColumn(ParseState *pstate, Li *** 771,776 **** --- 785,794 ---- retval = (Node *) wfunc; } + /* if it returns a set, remember it for error checks at higher levels */ + if (retset) + pstate->p_last_srf = retval; + return retval; } *************** LookupAggWithArgs(ObjectWithArgs *agg, b *** 2083,2091 **** * and throw a nice error if not. * * A side-effect is to set pstate->p_hasTargetSRFs true if appropriate. */ void ! check_srf_call_placement(ParseState *pstate, int location) { const char *err; bool errkind; --- 2101,2113 ---- * and throw a nice error if not. * * A side-effect is to set pstate->p_hasTargetSRFs true if appropriate. + * + * last_srf should be a copy of pstate->p_last_srf from just before we + * started transforming the function's arguments. This allows detection + * of whether the SRF's arguments contain any SRFs. */ void ! check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) { const char *err; bool errkind; *************** check_srf_call_placement(ParseState *pst *** 2121,2127 **** errkind = true; break; case EXPR_KIND_FROM_FUNCTION: ! /* okay ... but we can't check nesting here */ break; case EXPR_KIND_WHERE: errkind = true; --- 2143,2157 ---- errkind = true; break; case EXPR_KIND_FROM_FUNCTION: ! /* okay, but we don't allow nested SRFs here */ ! /* errmsg is chosen to match transformRangeFunction() */ ! /* errposition should point to the inner SRF */ ! if (pstate->p_last_srf != last_srf) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("set-returning functions must appear at top level of FROM"), ! parser_errposition(pstate, ! exprLocation(pstate->p_last_srf)))); break; case EXPR_KIND_WHERE: errkind = true; diff --git a/src/backend/parser/parse_oper.c b/src/backend/parser/parse_oper.c index e40b10d..4b1db76 100644 *** a/src/backend/parser/parse_oper.c --- b/src/backend/parser/parse_oper.c *************** op_error(ParseState *pstate, List *op, c *** 735,746 **** * Transform operator expression ensuring type compatibility. * This is where some type conversion happens. * ! * As with coerce_type, pstate may be NULL if no special unknown-Param ! * processing is wanted. */ Expr * make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree, ! int location) { Oid ltypeId, rtypeId; --- 735,748 ---- * Transform operator expression ensuring type compatibility. * This is where some type conversion happens. * ! * last_srf should be a copy of pstate->p_last_srf from just before we ! * started transforming the operator's arguments; this is used for nested-SRF ! * detection. If the caller will throw an error anyway for a set-returning ! * expression, it's okay to cheat and just pass pstate->p_last_srf. */ Expr * make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree, ! Node *last_srf, int location) { Oid ltypeId, rtypeId; *************** make_op(ParseState *pstate, List *opname *** 843,849 **** /* if it returns a set, check that's OK */ if (result->opretset) ! check_srf_call_placement(pstate, location); ReleaseSysCache(tup); --- 845,855 ---- /* if it returns a set, check that's OK */ if (result->opretset) ! { ! check_srf_call_placement(pstate, last_srf, location); ! /* ... and remember it for error checks at higher levels */ ! pstate->p_last_srf = (Node *) result; ! } ReleaseSysCache(tup); diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index 5be1812..68572e9 100644 *** a/src/include/parser/parse_func.h --- b/src/include/parser/parse_func.h *************** typedef enum *** 31,37 **** extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, ! FuncCall *fn, int location); extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, --- 31,37 ---- extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, ! Node *last_srf, FuncCall *fn, int location); extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, *************** extern Oid LookupFuncWithArgs(ObjectWith *** 67,72 **** extern Oid LookupAggWithArgs(ObjectWithArgs *agg, bool noError); ! extern void check_srf_call_placement(ParseState *pstate, int location); #endif /* PARSE_FUNC_H */ --- 67,73 ---- extern Oid LookupAggWithArgs(ObjectWithArgs *agg, bool noError); ! extern void check_srf_call_placement(ParseState *pstate, Node *last_srf, ! int location); #endif /* PARSE_FUNC_H */ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 0b54840..6a3507f 100644 *** a/src/include/parser/parse_node.h --- b/src/include/parser/parse_node.h *************** typedef Node *(*CoerceParamHook) (ParseS *** 157,162 **** --- 157,165 ---- * p_hasAggs, p_hasWindowFuncs, etc: true if we've found any of the indicated * constructs in the query. * + * p_last_srf: the set-returning FuncExpr or OpExpr most recently found in + * the query, or NULL if none. + * * p_pre_columnref_hook, etc: optional parser hook functions for modifying the * interpretation of ColumnRefs and ParamRefs. * *************** struct ParseState *** 199,204 **** --- 202,209 ---- bool p_hasSubLinks; bool p_hasModifyingCTE; + Node *p_last_srf; /* most recent set-returning func/op found */ + /* * Optional hook functions for parser callbacks. These are null unless * set up by the caller of make_parsestate. diff --git a/src/include/parser/parse_oper.h b/src/include/parser/parse_oper.h index d783b37..ab3c4aa 100644 *** a/src/include/parser/parse_oper.h --- b/src/include/parser/parse_oper.h *************** extern Oid oprfuncid(Operator op); *** 59,65 **** /* Build expression tree for an operator invocation */ extern Expr *make_op(ParseState *pstate, List *opname, ! Node *ltree, Node *rtree, int location); extern Expr *make_scalar_array_op(ParseState *pstate, List *opname, bool useOr, Node *ltree, Node *rtree, int location); --- 59,65 ---- /* Build expression tree for an operator invocation */ extern Expr *make_op(ParseState *pstate, List *opname, ! Node *ltree, Node *rtree, Node *last_srf, int location); extern Expr *make_scalar_array_op(ParseState *pstate, List *opname, bool useOr, Node *ltree, Node *rtree, int location); diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 4b6170d..5c82614 100644 *** a/src/test/regress/expected/rangefuncs.out --- b/src/test/regress/expected/rangefuncs.out *************** select * from foobar(); -- fail *** 1969,1986 **** ERROR: function return row and query-specified return row do not match DETAIL: Returned row contains 3 attributes, but query expects 2. drop function foobar(); - -- check behavior when a function's input sometimes returns a set (bug #8228) - SELECT *, - lower(CASE WHEN id = 2 THEN (regexp_matches(str, '^0*([1-9]\d+)$'))[1] - ELSE str - END) - FROM - (VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str); - id | str | lower - ----+---------------+------- - 2 | 0000000049404 | 49404 - (1 row) - -- check whole-row-Var handling in nested lateral functions (bug #11703) create function extractq2(t int8_tbl) returns int8 as $$ select t.q2 --- 1969,1974 ---- diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index c8ae361..10cacc5 100644 *** a/src/test/regress/expected/tsrf.out --- b/src/test/regress/expected/tsrf.out *************** SELECT generate_series(1, generate_serie *** 41,46 **** --- 41,51 ---- 3 (6 rows) + -- but we've traditionally rejected the same in FROM + SELECT * FROM generate_series(1, generate_series(1, 3)); + ERROR: set-returning functions must appear at top level of FROM + LINE 1: SELECT * FROM generate_series(1, generate_series(1, 3)); + ^ -- srf, with two SRF arguments SELECT generate_series(generate_series(1,3), generate_series(2, 4)); generate_series *************** SELECT few.dataa, count(*) FROM few WHER *** 190,203 **** a | 4 (2 rows) -- SRFs are not allowed in aggregate arguments SELECT min(generate_series(1, 3)) FROM few; ! ERROR: set-valued function called in context that cannot accept a set LINE 1: SELECT min(generate_series(1, 3)) FROM few; ^ -- SRFs are not allowed in window function arguments, either SELECT min(generate_series(1, 3)) OVER() FROM few; ! ERROR: set-valued function called in context that cannot accept a set LINE 1: SELECT min(generate_series(1, 3)) OVER() FROM few; ^ -- SRFs are normally computed after window functions --- 195,217 ---- a | 4 (2 rows) + -- SRFs are not allowed if they'd need to be conditionally executed + SELECT q1, case when q1 > 0 then generate_series(1,3) else 0 end FROM int8_tbl; + ERROR: set-returning functions are not allowed in CASE + LINE 1: SELECT q1, case when q1 > 0 then generate_series(1,3) else 0... + ^ + SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl; + ERROR: set-returning functions are not allowed in COALESCE + LINE 1: SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl; + ^ -- SRFs are not allowed in aggregate arguments SELECT min(generate_series(1, 3)) FROM few; ! ERROR: aggregate function calls cannot contain set-returning function calls LINE 1: SELECT min(generate_series(1, 3)) FROM few; ^ -- SRFs are not allowed in window function arguments, either SELECT min(generate_series(1, 3)) OVER() FROM few; ! ERROR: window function calls cannot contain set-returning function calls LINE 1: SELECT min(generate_series(1, 3)) OVER() FROM few; ^ -- SRFs are normally computed after window functions *************** SELECT int4mul(generate_series(1,2), 10) *** 427,433 **** -- but SRFs in function RTEs must be at top level (annoying restriction) SELECT * FROM int4mul(generate_series(1,2), 10); ! ERROR: set-valued function called in context that cannot accept a set LINE 1: SELECT * FROM int4mul(generate_series(1,2), 10); ^ -- DISTINCT ON is evaluated before tSRF evaluation if SRF is not --- 441,447 ---- -- but SRFs in function RTEs must be at top level (annoying restriction) SELECT * FROM int4mul(generate_series(1,2), 10); ! ERROR: set-returning functions must appear at top level of FROM LINE 1: SELECT * FROM int4mul(generate_series(1,2), 10); ^ -- DISTINCT ON is evaluated before tSRF evaluation if SRF is not diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql index 4ed84b1..442d397 100644 *** a/src/test/regress/sql/rangefuncs.sql --- b/src/test/regress/sql/rangefuncs.sql *************** select * from foobar(); -- fail *** 600,614 **** drop function foobar(); - -- check behavior when a function's input sometimes returns a set (bug #8228) - - SELECT *, - lower(CASE WHEN id = 2 THEN (regexp_matches(str, '^0*([1-9]\d+)$'))[1] - ELSE str - END) - FROM - (VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str); - -- check whole-row-Var handling in nested lateral functions (bug #11703) create function extractq2(t int8_tbl) returns int8 as $$ --- 600,605 ---- diff --git a/src/test/regress/sql/tsrf.sql b/src/test/regress/sql/tsrf.sql index 417e78c..45de099 100644 *** a/src/test/regress/sql/tsrf.sql --- b/src/test/regress/sql/tsrf.sql *************** SELECT generate_series(1, 2), generate_s *** 14,19 **** --- 14,22 ---- -- srf, with SRF argument SELECT generate_series(1, generate_series(1, 3)); + -- but we've traditionally rejected the same in FROM + SELECT * FROM generate_series(1, generate_series(1, 3)); + -- srf, with two SRF arguments SELECT generate_series(generate_series(1,3), generate_series(2, 4)); *************** SELECT dataa, generate_series(1,1), coun *** 51,56 **** --- 54,63 ---- SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa ORDER BY 2; SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa, unnest('{1,1,3}'::int[]) ORDER BY 2; + -- SRFs are not allowed if they'd need to be conditionally executed + SELECT q1, case when q1 > 0 then generate_series(1,3) else 0 end FROM int8_tbl; + SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl; + -- SRFs are not allowed in aggregate arguments SELECT min(generate_series(1, 3)) FROM few;