Re: Expanding DELETE/UPDATE returning

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> On Mon, Feb 26, 2007 at 11:14:01PM -0500, Tom Lane wrote:
>> Rusty Conover <rconover(at)infogears(dot)com> writes:
>>> Or allow delete and update to be used in sub-queries?
>>
>> That's been discussed but the implementation effort seems far from
>> trivial. One big problem is that a sub-query can normally be
>> re-executed multiple times, eg on the inner side of a join; whereas
>> that's clearly not acceptable for an insert/update/delete.

> Couldn't we avoid that by writing the data to a tuplestore? Or is it too
> hard to detect the cases when that would need to happen?

I said it wasn't trivial, not that it wasn't doable. Offhand I think
you'd need a tuplestore buffer, plus a major refactoring of the executor
toplevel --- because currently, all types of updating queries are
hardwired for the actual update to occur only at top level. There are
also some definitional issues: when do you think triggers should fire?
What happens if the outer query contains a LIMIT clause that a naive
user would think causes the updating subquery not to be read all the way
to the end? If there are multiple updating subqueries in the same outer
query, how do they interact?

I don't say it's insoluble, just not trivial ...

regards, tom lane