Re: wCTE behaviour

On 2010-11-13 5:36 PM +0200, Clark C. Evans wrote:
> On Sat, 13 Nov 2010 17:23 +0200, "Marko Tiikkaja" wrote:
>> So these queries would behave differently?
>>
>> WITH t AS (DELETE FROM foo RETURNING *)
>> SELECT 1 WHERE false;
>>
>> WITH t AS (DELETE FROM foo RETURNING *)
>> SELECT 1 FROM t LIMIT 0;
>
> I'm still trying to wrap my head around this
> new mechanism.  What would this return?
>
> UPDATE foo SET access = 0;
>
> WITH t AS (UPDATE foo SET access = access + 1 RETURNING *)
> SELECT x.access, y.access
>   FROM t CROSS JOIN t;

I'm assuming you forgot to give the tables aliases:

WITH t AS (UPDATE foo SET access = access + 1 RETURNING *)
SELECT x.access, y.access
    FROM t x CROSS JOIN t y;

This would return n * n rows with values (1,1) where n is the number of 
rows in foo when the snapshot was taken.  I.e. every row in foo would 
now have access=1.  I'm also ignoring the possibility that someone 
modified the table between those two queries.


Regards,
Marko Tiikkaja