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