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(at)gmx(dot)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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +