Re: UPDATE SET (a,b,c) = (SELECT ...) versus rules

Martijn van Oosterhout <kleptog@svana.org> writes:
> Would it not be possible to use WITH here, like:

> WITH bar AS ( ... subselect ... )
> INSERT INTO foolog VALUES (bar.a, bar.b, ...)

Don't think it works if the sub-select is correlated.

Consider something like

UPDATE summary_table s SET (sumx, sumy) = (SELECT sum(x), sum(y) FROM detail_table d                     WHERE d.group
=s.group)
 

and suppose we have a logging rule like the above on summary_table.
You can't push the sub-select into a WITH because it depends on
s.group.  With sufficient intelligence you could rewrite the query
entirely, I guess, but no simple transformation is going to cope.

But come to think of it, WITH is already an interesting precedent: if you
look into rewriteHandler.c you'll notice a boatload of corner cases where
the rewriter just throws up its hands for various combinations of rules
and statements containing WITH.  So maybe that lends a bit more weight
to Andres' position that it's okay to consider this an unimplemented
feature.
        regards, tom lane