Re: Why is UPDATE with column-list syntax not implemented

On Fri, Nov 22, 2013 at 6:36 PM, AK <alkuzo(at)gmail(dot)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).