Hi, here is a patch that extends update syntax following the sql standard. The patch includes sgml documentation, too.
For example: UPDATE table SET (col1, col2, ...) = (val1, val2, ...), (colm, coln, ...) = (valm, valn, ...), ...; Susanne
Index: doc/src/sgml/ref/update.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.37
diff -u -3 -p -c -r1.37 update.sgml
*** doc/src/sgml/ref/update.sgml 8 Mar 2006 22:59:09 -0000 1.37
--- doc/src/sgml/ref/update.sgml 19 Jul 2006 13:01:48 -0000
*************** UPDATE [ ONLY ] <replaceable class="PARA
*** 25,30 ****
--- 25,36 ----
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
</synopsis>
+ <synopsis>
+ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
+ SET ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...]
+ [ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
+ [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
+ </synopsis>
</refsynopsisdiv>
<refsect1>
*************** UPDATE films SET kind = 'Dramatic' WHERE
*** 210,215 ****
--- 216,225 ----
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
WHERE city = 'San Francisco' AND date = '2003-07-03';
</programlisting>
+ <programlisting>
+ UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
+ WHERE city = 'San Francisco' AND date = '2003-07-03';
+ </programlisting>
</para>
<para>
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.551
diff -u -3 -p -c -r2.551 gram.y
*** src/backend/parser/gram.y 3 Jul 2006 22:45:39 -0000 2.551
--- src/backend/parser/gram.y 19 Jul 2006 13:01:55 -0000
*************** static void doNegateFloat(Value *v);
*** 237,243 ****
name_list from_clause from_list opt_array_bounds
qualified_name_list any_name any_name_list
any_operator expr_list attrs
! target_list update_target_list insert_column_list
insert_target_list def_list indirection opt_indirection
group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
--- 237,244 ----
name_list from_clause from_list opt_array_bounds
qualified_name_list any_name any_name_list
any_operator expr_list attrs
! target_list update_col_list update_target_list
! update_value_list insert_column_list
insert_target_list def_list indirection opt_indirection
group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
*************** static void doNegateFloat(Value *v);
*** 308,314 ****
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
! %type <target> target_el insert_target_el update_target_el insert_column_item
%type <typnam> Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
--- 309,317 ----
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
! %type <target> target_el insert_target_el update_target_el update_col_list_el
! insert_column_item
! %type <list> update_target_lists_list update_target_lists_el
%type <typnam> Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
*************** UpdateStmt: UPDATE relation_expr_opt_ali
*** 5477,5482 ****
--- 5480,5497 ----
n->whereClause = $6;
$$ = (Node *)n;
}
+ | UPDATE relation_expr_opt_alias
+ SET update_target_lists_list
+ from_clause
+ where_clause
+ {
+ UpdateStmt *n = makeNode(UpdateStmt);
+ n->relation = $2;
+ n->targetList = $4;
+ n->fromClause = $5;
+ n->whereClause = $6;
+ $$ = (Node *)n;
+ }
;
*************** target_el: a_expr AS ColLabel
*** 8165,8171 ****
;
update_target_list:
! update_target_el { $$ = list_make1($1); }
| update_target_list ',' update_target_el { $$ = lappend($1,$3); }
;
--- 8180,8186 ----
;
update_target_list:
! update_target_el { $$ = list_make1($1); }
| update_target_list ',' update_target_el { $$ = lappend($1,$3); }
;
*************** update_target_el:
*** 8186,8192 ****
--- 8201,8261 ----
$$->val = (Node *) makeNode(SetToDefault);
$$->location = @1;
}
+ ;
+
+ update_target_lists_list:
+ update_target_lists_el { $$ = $1; }
+ | update_target_lists_list ',' update_target_lists_el { $$ = list_concat($1, $3); }
+
+ update_target_lists_el:
+ '(' update_col_list ')' '=' '(' update_value_list ')'
+ {
+ ListCell *col_cell = NULL;
+ ListCell *val_cell = NULL;
+
+ if (list_length($2) != list_length($6))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("number of columns does not match to number of values")));
+ }
+
+ for(col_cell = list_head($2), val_cell = list_head($6);
+ col_cell != NULL && val_cell !=NULL;
+ col_cell = lnext(col_cell), val_cell = lnext(val_cell))
+ {
+ /*
+ * merge update_value_list with update_col_list
+ */
+ ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+ ResTarget *res_val = (ResTarget *) lfirst(val_cell);
+
+ res_col->val = (Node *)copyObject(res_val->val);
+ }
+
+ $$ = $2;
+ }
+ ;
+
+ update_col_list:
+ update_col_list_el { $$ = list_make1($1); }
+ | update_col_list ',' update_col_list_el { $$ = lappend($1, $3); }
+ ;
+
+ update_col_list_el:
+ ColId opt_indirection
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $1;
+ $$->indirection = $2;
+ $$->val = NULL;
+ $$->location = @1;
+ }
+ ;
+ update_value_list:
+ insert_target_el { $$ = list_make1($1); }
+ | update_value_list ',' insert_target_el { $$ = lappend($1, $3); }
;
insert_target_list:
signature.asc
Description: This is a digitally signed message part
