Re: [HACKERS] extension for sql update

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   bruce@momjian.us
  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); }
          ;