Re: wCTE behaviour

On 2010-11-11 17:50, Marko Tiikkaja wrote:
> Just to be clear, the main point is whether they see the data 
> modifications or not.  The simplest case to point out this behaviour is:
>
> WITH t AS (DELETE FROM foo)
> SELECT * FROM foo;
>
> And the big question is: what state of "foo" should the SELECT 
> statement see?
Since t is not referenced in the query, foo should not be deleted at 
all, like
WITH t AS (SELECT nextval('seq'))
SELECT * FROM foo
does not update the sequence.

But if t is referenced..
WITH t AS (DELETE FROM foo RETURNING *)
SELECT * FROM foo NATURAL JOIN t;

Since the extension of t can only be known by deleting foo, it makes 
sense that this query cannot return rows. "Select the rows from foo that 
I just deleted."

regards,
Yeb Havinga