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

Martijn van Oosterhout <kleptog(at)svana(dot)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