Why is UPDATE with column-list syntax not implemented

AK wrote

9.3 documentation says:

According to the standard, the column-list syntax should allow a list of
columns to be assigned from a single row-valued expression, such as a
sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
This is not currently implemented — the source must be a list of
independent expressions.

Why is this not implemented? Is it considered inconvenient to use, or
difficult to implement. or not important enough, or some other reason?

I cannot answer why but I too would like to see this. I actually asked this
a long while back but cannot seem to find my posting or recall the response.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779601.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Nov 21, 2013 at 3:50 PM, David Johnston <polobo@yahoo.com> wrote:

Why is this not implemented? Is it considered inconvenient to use, or
difficult to implement. or not important enough, or some other reason?

I cannot answer why but I too would like to see this. I actually asked this
a long while back but cannot seem to find my posting or recall the response.

At least on this case, rules could implement this. Though figuring out
if whether it's the case or not might be harder.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

AK <alkuzo@gmail.com> writes:

9.3 documentation says:
According to the standard, the column-list syntax should allow a list of
columns to be assigned from a single row-valued expression, such as a
sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
This is not currently implemented — the source must be a list of independent
expressions.

Why is this not implemented? Is it considered inconvenient to use, or
difficult to implement. or not important enough, or some other reason?

It's difficult to implement. You'd need to do some significant
restructuring of the way UPDATE is handled. Probably someone will
attempt it at some point.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Claudio,

Can you elaborate how rules can help?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779896.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you, Tom!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779899.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Nov 22, 2013 at 6:36 PM, AK <alkuzo@gmail.com> wrote:

Claudio,

Can you elaborate how rules can help?

Well... that specific example:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);

Can be rewritten as

UPDATE accounts SET contact_last_name = t.last_name,
contact_first-name = t.first_name
FROM (SELECT salesmen.id as salesmen_id, last_name, first_name FROM salesmen) t
WHERE t.salesmen_id = accounts.sales_id;

That's not 100% general, but it's quite general enough, transforming:

UPDATE <T> SET (<field_list>) = (SELECT <field_list_b> <from_expr>
WHERE <T>.<F> = <join_expr> <filter_expr>)

Into

UPDATE <T> SET <field_n = tmp.field_b_n for all n> FROM (SELECT
<join_expr> AS <T>_<F>, <field_list_b> <from_expr> WHERE
<filter_expr>) tmp WHERE <T>.<F> = tmp.<T>_<F>;

That's *almost* a regex.

It's possible the transformation can be done at the AST-level more
generally, but I don't know enough of postgres parser to go deeper
into that path, but the general idea being that it can be done even
more generally with CTEs, if the where clause terms that relate to the
updated table can be pinpointed and extracted into the CTE (as long as
they're stable).

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Claudio,

Unfortunately, this UPDATE...FROM approach does not detect ambiguities,
unless we go for tricks.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5780215.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers