Re: extension for sql update

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Susanne Ebrecht <miracee(at)miracee(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: extension for sql update
Date: 2006-07-26 20:58:44
Message-ID: 18012.1153947524@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2006-07-26 21:01:15 Re: [HACKERS] [PATCH] Provide 8-byte transaction IDs to
Previous Message Diogo Biazus 2006-07-26 20:43:33 Re: xlogdump behaviour translating dropped relations

Browse pgsql-patches by date

  From Date Subject
Next Message Hannu Krosing 2006-07-26 21:01:15 Re: [HACKERS] [PATCH] Provide 8-byte transaction IDs to
Previous Message Darcy Buskermolen 2006-07-26 20:41:09 Re: [HACKERS] [PATCH] Provide 8-byte transaction IDs to user level