Re: extension for sql update

Susanne Ebrecht <miracee(at)miracee(dot)de> writes:
> here is a patch that extends update syntax following the sql standard.
> The patch includes sgml documentation, too.

> UPDATE table SET (col1, col2, ...) = (val1, val2, ...),
> (colm, coln, ...) = (valm, valn, ...), ...;

This is a cute hack, but it does only a small part of what I think the
spec says.

In the first place, the SQL syntax is pretty clear that you can combine
simple and multiple assignment in the same UPDATE:

<update statement: searched> ::=
UPDATE <target table>
SET <set clause list>
[ WHERE <search condition> ]

<set clause list> ::=
<set clause> [ { <comma> <set clause> }... ]

<set clause> ::=
<multiple column assignment>
| <set target> <equals operator> <update source>

<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>

<set target list> ::=
<left paren> <set target> [ { <comma> <set target> } ... ] <right paren>

The patch doesn't do that, but it wouldn't be too hard to fix. The more
serious problem is that

<assigned row> ::= <contextually typed row value expression>

and <contextually typed row value expression> is supposed to be pretty
much anything that can generate a row. The patch as you have it
provides nothing more than syntactic sugar for something people can do
anyway. 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);

and with something like that you can't break apart the row-valued
expression in the grammar. So in reality the feature has to propagate
much further into the backend than this.

regards, tom lane