Re: [PATCHES] extension for sql update

Are we sure we don't want the patch for a non-subquery version of SET
ROW for 8.2?
       o Allow UPDATE tab SET ROW (col, ...) = (...) for updating multiple         columns

---------------------------------------------------------------------------

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
> >> The reason people want this syntax is that they expect to be
> >> able to write, say,
> >> UPDATE mytab SET (foo, bar, baz) =
> >> (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);
> 
> > I don't find any derivation in the standard that would permit this.
> 
> Well, there are two ways to get there.  SQL99 does not actually have the
> syntax with parentheses on the left, but what it does have is SET ROW:
> 
>          <set clause> ::=
>                 <update target> <equals operator> <update source>
>               | <mutated set clause> <equals operator> <update source>
> 
>          <update target> ::=
>                 <object column>
>               | ROW
>               | <object column>
>                   <left bracket or trigraph> <simple value specification> <right bracket or trigraph>
> 
>          <update source> ::=
>                 <value expression>
>               | <contextually typed value specification>
> 
> and you can derive (SELECT ...) from <value expression> via
> 
>          <value expression> ::=
>         ...
>               | <row value expression>
> 
>          <row value expression> ::=
>         ...
>               | <row value constructor>
> 
>          <row value constructor> ::=
>         ...
>               | <row subquery>
> 
>          <row subquery> ::= <subquery>
> 
>          <subquery> ::=
>               <left paren> <query expression> <right paren>
> 
>          <query expression> ::=
>               [ <with clause> ] <query expression body>
> 
>          <query expression body> ::=
>                 <non-join query expression>
> 
>          <non-join query expression> ::=
>                 <non-join query term>
> 
>          <non-join query term> ::=
>                 <non-join query primary>
> 
>          <non-join query primary> ::=
>                 <simple table>
> 
>          <simple table> ::=
>                 <query specification>
> 
>          <query specification> ::=
>               SELECT [ <set quantifier> ] <select list>
>                 <table expression>
> 
> Another interesting restriction in SQL99 is
> 
>          9) If an <update target> specifies ROW, then:
> 
>             a) <set clause list> shall consist of exactly one <set clause>
>               SC.
> 
> SQL2003 seems to have dropped the ROW syntax entirely, but instead they
> have 
> 
>     <set clause> ::= <multiple column assignment>
> 
>     <multiple column assignment> ::=
>         <set target list> <equals operator> <assigned row>
> 
>     <assigned row> ::= <contextually typed row value expression>
> 
> and from there it goes through just like before.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +