Hi all,
Sorry for the delay.
Please find attached latest version of UPDATE (*) patch.The patch
implements review comments and Tom's gripe about touching
transformTargetList is addressed now. I have added regression tests and
simplified parser representation a bit.
Regards,
Atri
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 35b0699..1f68bdf 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -25,7 +25,9 @@ PostgreSQL documentation
UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
- ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
+ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> ) |
+ ( <replaceable class="PARAMETER">*</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
+ ( <replaceable class="PARAMETER">*</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
} [, ...]
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index a68f2e8..6d08dbd 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1937,6 +1937,101 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
+ /*
+ * Check if (SET(*) = SELECT ...) is present. If it is present we
+ * need to resolve and populate the remaining needed MultiAssignRefs in the
+ * target list. We modify target list in place and add needed MultiAssignRefs.
+ */
+ if (list_length(stmt->targetList) == 1)
+ {
+ ResTarget *current_val = linitial(stmt->targetList);
+
+ /* Currently there is no way that ResTarget node's name value in UPDATE command
+ * is set to NULL except for UPDATE SET (*) case.
+ * Hence we can safely depend on name value being NULL as a check for
+ * presence of UPDATE SET (*) case.
+ */
+ if (current_val->name == NULL)
+ {
+ List *rel_cols_list;
+ List *expanded_tlist = NULL;
+ List *sub_list = NULL;
+ ListCell *lc_val;
+ ListCell *lc_relcol;
+ int rteindex = 0;
+ int sublevels_up = 0;
+ int i = 0;
+
+ rteindex = RTERangeTablePosn(pstate, pstate->p_target_rangetblentry,
+ &sublevels_up);
+
+ expandRTE(pstate->p_target_rangetblentry, rteindex, sublevels_up,
+ current_val->location, false,
+ &(rel_cols_list), NULL);
+
+
+ /* SET(*) = (SELECT ...) case */
+ if (IsA(current_val->val, MultiAssignRef))
+ {
+ MultiAssignRef *orig_val = (MultiAssignRef *) (current_val->val);
+
+ orig_val->ncolumns = list_length(rel_cols_list);
+
+ Assert(sub_list == NULL);
+
+ sub_list = list_make1(orig_val);
+
+ /* Change targetlist to have corresponding ResTarget nodes
+ * as corresponding to the columns in target relation */
+ for (i = 1;i < list_length(rel_cols_list);i++)
+ {
+ MultiAssignRef *r = makeNode(MultiAssignRef);
+
+ r->source = orig_val->source;
+ r->colno = i + 1;
+ r->ncolumns = orig_val->ncolumns;
+
+ lappend(sub_list, r);
+ }
+ }
+ else if (IsA(current_val->val, List)) /* SET(*) = (val, val,...) case */
+ {
+
+ Assert(sub_list == NULL);
+ sub_list = (List *) (current_val->val);
+ }
+ else
+ {
+ elog(ERROR, "Unknown type in UPDATE command");
+ }
+
+ if (list_length(rel_cols_list) != list_length(sub_list))
+ elog(ERROR, "number of columns does not match number of values");
+
+ /* Change targetlist to have corresponding ResTarget nodes
+ * as corresponding to the columns in target relation */
+ forboth(lc_val, sub_list, lc_relcol, rel_cols_list)
+ {
+ ResTarget *current_res = makeNode(ResTarget);
+
+ current_res->name = strVal(lfirst(lc_relcol));
+ current_res->val = (Node *) (lfirst(lc_val));
+
+ if (expanded_tlist == NULL)
+ {
+ expanded_tlist = list_make1(current_res);
+ }
+ else
+ {
+ lappend(expanded_tlist, current_res);
+ }
+ }
+
+ stmt->targetList = expanded_tlist;
+ }
+ }
+
+
qry->targetList = transformTargetList(pstate, stmt->targetList,
EXPR_KIND_UPDATE_SOURCE);
@@ -1982,18 +2077,20 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
continue;
}
if (origTargetList == NULL)
- elog(ERROR, "UPDATE target count mismatch --- internal error");
+ elog(ERROR, "UPDATE target count mismatch --- internal error");
+
origTarget = (ResTarget *) lfirst(origTargetList);
Assert(IsA(origTarget, ResTarget));
attrno = attnameAttNum(pstate->p_target_relation,
- origTarget->name, true);
+ origTarget->name, true);
+
if (attrno == InvalidAttrNumber)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation \"%s\" does not exist",
origTarget->name,
- RelationGetRelationName(pstate->p_target_relation)),
+ RelationGetRelationName(pstate->p_target_relation)),
parser_errposition(pstate, origTarget->location)));
updateTargetListEntry(pstate, tle, origTarget->name,
@@ -2008,7 +2105,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
origTargetList = lnext(origTargetList);
}
if (origTargetList != NULL)
- elog(ERROR, "UPDATE target count mismatch --- internal error");
+ elog(ERROR, "UPDATE target count mismatch --- internal error");
assign_query_collations(pstate, qry);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 36dac29..25aa857 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -9629,6 +9629,58 @@ multiple_set_clause:
$$ = $2;
}
+| '(' '*' ')' '=' ctext_row
+{
+ ResTarget *res_col = makeNode(ResTarget);
+
+ /* We cheat a little here by making a single Restarget node but assigning the entire ctext_row
+ * value to it. This is done since we cannot resolve attribute names at this stage */
+ res_col->name = NULL;
+ res_col->val = (Node *) $5;
+ res_col->location = @2;
+
+ $$ = list_make1(res_col);
+ }
+| '(' '*' ')' '=' select_with_parens
+{
+ SubLink *sl = makeNode(SubLink);
+ int ncolumns = -1; /* We do not know the number of columns yet */
+
+ /*
+ * Create a MultiAssignRef source as representative for the entire set
+ * since we cannot look up attributes of target relation here.
+ * A ResTarget node is made and MultiAssignRef source is assigned
+ * as the ResTarget node's val.
+ * Since we do not have column names at this stage, we shall have
+ * column name expanding in parser transformation stage.
+ * We make Lists at each level so that we can detect we have a
+ * * in transformation stage.
+ * We currently do not have any place in the code where we have a ResTarget
+ * node in UPDATE command with name value as NULL. Hence, we can safely make a dummy ResTarget node
+ * here and set name value to NULL and check it later in parser transformation
+ * stage.
+ */
+ ResTarget *res_col = makeNode(ResTarget);
+ MultiAssignRef *r = makeNode(MultiAssignRef);
+
+ /* First, convert bare SelectStmt into a SubLink */
+ sl->subLinkType = MULTIEXPR_SUBLINK;
+ sl->subLinkId = 0; /* will be assigned later */
+ sl->testexpr = NULL;
+ sl->operName = NIL;
+ sl->subselect = $5;
+ sl->location = @5;
+
+ r->source = (Node *) sl;
+ r->colno = 1;
+ r->ncolumns = ncolumns;
+ res_col->val = (Node *) r;
+
+ res_col->name = NULL;
+ res_col->location = @2;
+
+ $$ = list_make1(res_col);
+ }
;
set_target:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f0f0488..953e05e 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1295,7 +1295,7 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
Assert(IsA(qtree, Query));
/* Check subquery returns required number of columns */
- if (count_nonjunk_tlist_entries(qtree->targetList) != maref->ncolumns)
+ if ((count_nonjunk_tlist_entries(qtree->targetList) != maref->ncolumns) && maref->ncolumns != -1)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("number of columns does not match number of values"),
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b1dfa85..4237a97 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -374,13 +374,17 @@ typedef struct A_ArrayExpr
* In an UPDATE target list, 'name' is the name of the destination column,
* 'indirection' stores any subscripts attached to the destination, and
* 'val' is the expression to assign.
+ * In an UPDATE operation namve value for ResTarget is not expected to be NULL
+ * hence we set name value to NULL in case of SET(*) case. Parser transformation
+ * code depends on name being NULL as a sure shot proof of SET(*) being present.
*
* See A_Indirection for more info about what can appear in 'indirection'.
*/
typedef struct ResTarget
{
NodeTag type;
- char *name; /* column name or NULL */
+ char *name; /* column name or NULL. Note that a NULL represents
+ * an UPDATE SET (*) case here*/
List *indirection; /* subscripts, field names, and '*', or NIL */
Node *val; /* the value expression to compute or assign */
int location; /* token location, or -1 if unknown */
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 1de2a86..1307e0e 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -6,8 +6,16 @@ CREATE TABLE update_test (
b INT,
c TEXT
);
+CREATE TABLE update_star (
+ a INT DEFAULT 10,
+ b INT,
+ c TEXT
+);
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
+INSERT INTO update_star VALUES(105, 107, 'test2');
+INSERT INTO update_star VALUES(112, 93, 'test3');
+INSERT INTO update_star VALUES(84, 97, 'test4');
SELECT * FROM update_test;
a | b | c
----+----+-----
@@ -15,6 +23,26 @@ SELECT * FROM update_test;
10 | 15 |
(2 rows)
+-- Check SET(*) case
+UPDATE update_star SET(*) = (SELECT * FROM update_star WHERE b = 107) WHERE b=97;
+SELECT a,b, char_length(c) FROM update_star;
+ a | b | char_length
+-----+-----+-------------
+ 105 | 107 | 5
+ 112 | 93 | 5
+ 105 | 107 | 5
+(3 rows)
+
+UPDATE update_star SET(*) = (DEFAULT, DEFAULT, DEFAULT) WHERE b = 93;
+SELECT * FROM update_star;
+ a | b | c
+-----+-----+-------
+ 105 | 107 | test2
+ 105 | 107 | test2
+ 10 | |
+(3 rows)
+
+DROP TABLE update_star;
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
a | b | c
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index e71128c..208ac7a 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -8,11 +8,32 @@ CREATE TABLE update_test (
c TEXT
);
+CREATE TABLE update_star (
+ a INT DEFAULT 10,
+ b INT,
+ c TEXT
+);
+
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
+INSERT INTO update_star VALUES(105, 107, 'test2');
+INSERT INTO update_star VALUES(112, 93, 'test3');
+INSERT INTO update_star VALUES(84, 97, 'test4');
+
SELECT * FROM update_test;
+-- Check SET(*) case
+UPDATE update_star SET(*) = (SELECT * FROM update_star WHERE b = 107) WHERE b=97;
+
+SELECT a,b, char_length(c) FROM update_star;
+
+UPDATE update_star SET(*) = (DEFAULT, DEFAULT, DEFAULT) WHERE b = 93;
+
+SELECT * FROM update_star;
+
+DROP TABLE update_star;
+
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
@@ -21,7 +42,6 @@ SELECT * FROM update_test;
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
-
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
