targetlist functions part 1 (was [HACKERS] targetlist functions proposals)
От | Joe Conway |
---|---|
Тема | targetlist functions part 1 (was [HACKERS] targetlist functions proposals) |
Дата | |
Msg-id | 3E21CF78.8030101@joeconway.com обсуждение исходный текст |
Ответ на | [Fwd: SETOF input parameters (was Re: [HACKERS] proposal: array utility functions phase 1)] (Joe Conway <mail@joeconway.com>) |
Ответы |
Re: targetlist functions part 1 (was [HACKERS] targetlist
(Peter Eisentraut <peter_e@gmx.net>)
Re: targetlist functions part 1 (was [HACKERS] targetlist (Bruce Momjian <pgman@candle.pha.pa.us>) Re: targetlist functions part 1 (was [HACKERS] targetlist (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | 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);
В списке pgsql-patches по дате отправления:
Следующее
От: "Christopher Kings-Lynne"Дата:
Сообщение: Add UNIQUE and PRIMARY KEY to pg_get_constraintdef