Re: Use select and update together

Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
> On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote:
>> select * from (
>> update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *
>> ) as x
>>
>> wouldn't work even in PG 9.1.
>> So what data structure is coming out of an "update ... returning *"
>> statement?
>> It obviously doesn't work like a subquery.

> The only way to make something like this work in 9.1 would be:

> WITH x AS
> (update tbl set val = 1 where key in ( 1, 2, 3, 4, 5 ) returning *)
> SELECT * FROM x;

The reason for that restriction is that WITH guarantees that the
contained query is evaluated once and only once, whereas an ordinary
subquery guarantees no such thing. So the effects of the UPDATE would
be quite unpredictable if we allowed the former syntax. (In the
specific example given it would likely work all right anyway, since
there is no reason for a plain SELECT FROM to do anything except scan
the subquery once. But if you did a join, say, watch out!)

regards, tom lane