Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

I wrote:
> Yes, we already have guards for those cases, but they return fairly opaque
> error messages to the tune of "set-valued function called in context that
> cannot accept a set", because the executor hasn't enough context to do
> better.  I'd like the messages to be more specific, like "set-valued
> function cannot appear within CASE" and so on.

Here's an expanded version of the "bottom up" patch that adjusts some
parser APIs to allow these additional messages to be thrown.  This changes
all occurrences of "set-valued function called in context that cannot
accept a set" in the core regression tests into something more specific.
There are probably some remaining cases that this doesn't cover, but the
existing execution-time checks will still catch those.

In passing, I added explicit checks that the operator doesn't return
set to transformAExprNullIf and make_distinct_op.  We used to be a bit
laissez-faire about that, expecting that the executor would throw
errors for such cases.  But now there are hard-wired assumptions that
only FuncExpr and OpExpr nodes could return sets, so we've got to make
sure that NullIfExpr and DistinctExpr nodes don't.

(In the other direction, I suspect that expression_returns_set() is now
too optimistic about believing that it needn't descend into the arguments
of nodes whose execution code used to reject SRF results.  But that's a
matter for a separate patch.)

            regards, tom lane

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;


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers