On Fri, Nov 22, 2013 at 6:36 PM, AK <alk...@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