Re: targetlist functions part 1 (was [HACKERS] targetlist - Mailing list pgsql-patches

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Joe Conway wrote:
> 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);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073