Re: targetlist functions part 1 (was [HACKERS] targetlist

Patch applied. Thanks.

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

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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