targetlist functions part 1 (was [HACKERS] targetlist functions proposals) - Mailing list pgsql-patches

Joe Conway wrote:
> =================================================================
> User interface proposal for multi-row function targetlist entries
> =================================================================
> 1. Only one targetlist entry may return a set.
> 2. Each targetlist item (other than the set returning one) is
>    repeated for each item in the returned set.
>

Having gotten no objections (actually, no response at all), I can only assume
no one had heartburn with this change. The attached patch covers the first of
the two proposals, i.e. restricting the target list to only one set returning
function.

It compiles cleanly, and passes all regression tests. If there are no
objections, please apply.

Any suggestions on where this should be documented (other than maybe sql-select)?

Thanks,

Joe

p.s. Here's what the previous example now looks like:
CREATE TABLE bar(f1 int, f2 text, f3 int);
INSERT INTO bar VALUES(1, 'Hello', 42);
INSERT INTO bar VALUES(2, 'Happy', 45);

CREATE TABLE foo(a int, b text);
INSERT INTO foo VALUES(42, 'World');
INSERT INTO foo VALUES(42, 'Everyone');
INSERT INTO foo VALUES(45, 'Birthday');
INSERT INTO foo VALUES(45, 'New Year');

CREATE TABLE foo2(a int, b text);
INSERT INTO foo2 VALUES(42, '!!!!');
INSERT INTO foo2 VALUES(42, '????');
INSERT INTO foo2 VALUES(42, '####');
INSERT INTO foo2 VALUES(45, '$$$$');

CREATE OR REPLACE FUNCTION getfoo(int) RETURNS SETOF text AS '
   SELECT b FROM foo WHERE a = $1
' language 'sql';

CREATE OR REPLACE FUNCTION getfoo2(int) RETURNS SETOF text AS '
   SELECT b FROM foo2 WHERE a = $1
' language 'sql';

regression=# SELECT f1, f2, getfoo(f3) AS f4 FROM bar;
  f1 |  f2   |    f4
----+-------+----------
   1 | Hello | World
   1 | Hello | Everyone
   2 | Happy | Birthday
   2 | Happy | New Year
(4 rows)

regression=# SELECT f1, f2, getfoo(f3) AS f4, getfoo2(f3) AS f5 FROM bar;
ERROR:  Only one target list entry may return a set result

Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.103
diff -c -r1.103 parse_clause.c
*** src/backend/parser/parse_clause.c    16 Dec 2002 18:39:22 -0000    1.103
--- src/backend/parser/parse_clause.c    12 Jan 2003 19:23:57 -0000
***************
*** 1121,1127 ****
       * the end of the target list.    This target is given resjunk = TRUE so
       * that it will not be projected into the final tuple.
       */
!     target_result = transformTargetEntry(pstate, node, expr, NULL, true);
      lappend(tlist, target_result);

      return target_result;
--- 1121,1127 ----
       * the end of the target list.    This target is given resjunk = TRUE so
       * that it will not be projected into the final tuple.
       */
!     target_result = transformTargetEntry(pstate, node, expr, NULL, true, NULL);
      lappend(tlist, target_result);

      return target_result;
Index: src/backend/parser/parse_target.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_target.c,v
retrieving revision 1.94
diff -c -r1.94 parse_target.c
*** src/backend/parser/parse_target.c    12 Dec 2002 20:35:13 -0000    1.94
--- src/backend/parser/parse_target.c    12 Jan 2003 19:25:16 -0000
***************
*** 42,54 ****
   * colname    the column name to be assigned, or NULL if none yet set.
   * resjunk    true if the target should be marked resjunk, ie, it is not
   *            wanted in the final projected tuple.
   */
  TargetEntry *
  transformTargetEntry(ParseState *pstate,
                       Node *node,
                       Node *expr,
                       char *colname,
!                      bool resjunk)
  {
      Oid            type_id;
      int32        type_mod;
--- 42,57 ----
   * colname    the column name to be assigned, or NULL if none yet set.
   * resjunk    true if the target should be marked resjunk, ie, it is not
   *            wanted in the final projected tuple.
+  * retset    if non-NULL, and the entry is a function expression, pass back
+  *            expr->funcretset
   */
  TargetEntry *
  transformTargetEntry(ParseState *pstate,
                       Node *node,
                       Node *expr,
                       char *colname,
!                      bool resjunk,
!                      bool *retset)
  {
      Oid            type_id;
      int32        type_mod;
***************
*** 61,66 ****
--- 64,72 ----
      if (IsA(expr, RangeVar))
          elog(ERROR, "You can't use relation names alone in the target list, try relation.*.");

+     if (retset && IsA(expr, FuncExpr))
+         *retset = ((FuncExpr *) expr)->funcretset;
+
      type_id = exprType(expr);
      type_mod = exprTypmod(expr);

***************
*** 93,102 ****
--- 99,110 ----
  List *
  transformTargetList(ParseState *pstate, List *targetlist)
  {
+     bool        retset = false;
      List       *p_target = NIL;

      while (targetlist != NIL)
      {
+         bool        entry_retset = false;
          ResTarget  *res = (ResTarget *) lfirst(targetlist);

          if (IsA(res->val, ColumnRef))
***************
*** 173,179 ****
                                                          res->val,
                                                          NULL,
                                                          res->name,
!                                                         false));
              }
          }
          else if (IsA(res->val, InsertDefault))
--- 181,188 ----
                                                          res->val,
                                                          NULL,
                                                          res->name,
!                                                         false,
!                                                         &entry_retset));
              }
          }
          else if (IsA(res->val, InsertDefault))
***************
*** 194,201 ****
                                                      res->val,
                                                      NULL,
                                                      res->name,
!                                                     false));
          }

          targetlist = lnext(targetlist);
      }
--- 203,217 ----
                                                      res->val,
                                                      NULL,
                                                      res->name,
!                                                     false,
!                                                     &entry_retset));
          }
+
+         if (retset && entry_retset)
+             elog(ERROR, "Only one target list entry may return a set result");
+
+         if (entry_retset)
+             retset = true;

          targetlist = lnext(targetlist);
      }
Index: src/include/parser/parse_target.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/parser/parse_target.h,v
retrieving revision 1.27
diff -c -r1.27 parse_target.h
*** src/include/parser/parse_target.h    18 Sep 2002 21:35:24 -0000    1.27
--- src/include/parser/parse_target.h    12 Jan 2003 19:08:56 -0000
***************
*** 20,26 ****
  extern List *transformTargetList(ParseState *pstate, List *targetlist);
  extern TargetEntry *transformTargetEntry(ParseState *pstate,
                       Node *node, Node *expr,
!                      char *colname, bool resjunk);
  extern void updateTargetListEntry(ParseState *pstate, TargetEntry *tle,
                        char *colname, int attrno,
                        List *indirection);
--- 20,26 ----
  extern List *transformTargetList(ParseState *pstate, List *targetlist);
  extern TargetEntry *transformTargetEntry(ParseState *pstate,
                       Node *node, Node *expr,
!                      char *colname, bool resjunk, bool *retset);
  extern void updateTargetListEntry(ParseState *pstate, TargetEntry *tle,
                        char *colname, int attrno,
                        List *indirection);