David Fetter <david@fetter.org> writes:
> On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote:
>> If that's what you want, you might as well just issue two separate
>> statements. There is no use-case for this at all unless the WITH
>> produces some RETURNING data that the SELECT makes use of.
> There are lots of use cases where it does exactly this.
Name *one*. If there is no RETURNING data, there is absolutely no
reason to use WITH instead of issuing the query separately. In fact,
I would assume that a DML query without RETURNING would not even be
syntactically legal in WITH.
> One simple
> example is maintaining a rollup table, so as less-rolled data get
> deleted, they get aggregated into an INSERT into that table.
Yes, exactly. The way you would do that is something like
WITH del AS (DELETE FROM foo WHERE whatever RETURNING *)INSERT INTO rollup SELECT * FROM del;
I am very interested to see how you will do the same thing without
using RETURNING and with the behavior you claim to want that the
DELETE is visibly complete before the INSERT starts. Where's the
INSERT gonna get the already-deleted data from?
With my proposal (ie, both queries using same snapshot) you could
actually do it without RETURNING, like this:
WITH useless_cte AS (DELETE FROM foo WHERE whatever)INSERT INTO rollup SELECT * FROM foo WHERE same-whatever;
But I don't see any reason to think that that's a superior way to write
the query, especially since it might be subject to weird race conditions
against other concurrent modifications of the table. RETURNING is just
a lot saner way to be sure that you're looking at exactly what the
DELETE deleted.
regards, tom lane