Jeff Davis <pgsql@j-davis.com> writes:
> On Fri, 2009-10-09 at 02:23 +0300, Peter Eisentraut wrote:
>> I think I'd want "writable subqueries" instead of only "writable CTEs".
> I think the original motivation was that it's more clear that a CTE is
> separated and can only be executed once (if it has side effects).
> Depending on how the query is written, it might be less obvious how many
> times the subquery should be executed, and it might change based on the
> plan.
Right. The behavior would be entirely unpredictable, and usually
undesirable, if the RETURNING query is underneath a join, or an
aggregate, or a LIMIT, yadda yadda. Tying it to WITH provides a
convenient way, from both the user-visible and implementation sides,
of saying "this is an independent query that we will execute once
and then make the RETURNING results available for use in this other
query".
regards, tom lane