Susanne Ebrecht wrote:
> >>>>>> Is it too hard to rip it back out once the full row support
> >>>>>> arrives? That seems speculation at best anyway.
> >>>>>>
> >>>>> That's what I was thinking. Glad someone else replied. ;-)
> >>>>>
> >>>> If you're looking for votes, +1. I'll gladly take a subset of the
> >>>> SQL standard UPDATE table SET (...) = (...) over having nothing.
> >>>>
> >>> +1 here, too. :)
> >>>
> >>>
> >> +1
> >>
> >
> > I am working now to get this into 8.2.
> >
> >
> I am glad to read this. But what does it mean to me? Shall I change the
> patch someway?
I have merged your patch into current CVS and applied it; attached.
There was quite a bit of code drift. One drift area was the new
RETURNING clause; that was easy to fix. A more complex case is the
code no longer has values as ResTargets --- it is a simple a_expr list,
so I changed the critical assignment in gram.y from:
res_col->val = (Node *)copyObject(res_val->val);
to:
res_col->val = (Node *)copyObject(res_val);
Hope that is OK. Without that fix, it crashed. I also merged your SGML
syntax and grammer addition into the exiting UPDATE main entry.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/update.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.38
retrieving revision 1.39
diff -c -r1.38 -r1.39
*** doc/src/sgml/ref/update.sgml 12 Aug 2006 02:52:03 -0000 1.38
--- doc/src/sgml/ref/update.sgml 2 Sep 2006 20:34:47 -0000 1.39
***************
*** 21,27 ****
<refsynopsisdiv>
<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> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
--- 21,28 ----
<refsynopsisdiv>
<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 } [, ...] |
! SET ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] ]
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
***************
*** 251,256 ****
--- 252,261 ----
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: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.560
retrieving revision 2.562
diff -c -r2.560 -r2.562
*** src/backend/parser/gram.y 2 Sep 2006 18:17:17 -0000 2.560
--- src/backend/parser/gram.y 2 Sep 2006 20:52:01 -0000 2.562
***************
*** 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
values_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 set_opt insert_column_list
values_list def_list indirection opt_indirection
group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
***************
*** 308,314 ****
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
! %type <target> target_el update_target_el insert_column_item
%type <typnam> Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
--- 309,316 ----
%type <jexpr> joined_table
%type <range> relation_expr
%type <range> relation_expr_opt_alias
! %type <target> 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
***************
*** 5524,5530 ****
*****************************************************************************/
UpdateStmt: UPDATE relation_expr_opt_alias
! SET update_target_list
from_clause
where_clause
returning_clause
--- 5526,5532 ----
*****************************************************************************/
UpdateStmt: UPDATE relation_expr_opt_alias
! SET set_opt
from_clause
where_clause
returning_clause
***************
*** 5539,5544 ****
--- 5541,5551 ----
}
;
+ set_opt:
+ update_target_list { $$ = $1; }
+ | update_target_lists_list { $$ = $1; }
+ ;
+
/*****************************************************************************
*
***************
*** 5941,5946 ****
--- 5948,6007 ----
| DEFAULT { $$ = (Node *) makeNode(SetToDefault); }
;
+ 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;
+ ListCell *val_cell;
+
+ 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);
+ Node *res_val = (Node *) lfirst(val_cell);
+
+ res_col->val = (Node *)copyObject(res_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:
+ values_item { $$ = list_make1($1); }
+ | update_value_list ',' values_item { $$ = lappend($1, $3); }
+ ;
+
/*****************************************************************************
*
***************
*** 8253,8259 ****
;
update_target_list:
! update_target_el { $$ = list_make1($1); }
| update_target_list ',' update_target_el { $$ = lappend($1,$3); }
;
--- 8314,8320 ----
;
update_target_list:
! update_target_el { $$ = list_make1($1); }
| update_target_list ',' update_target_el { $$ = lappend($1,$3); }
;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
