To: Guillaume Lelarge <guillaume@lelarge.info>
cc: Andreas <maps.on@gmx.net>, pgsql-sql@postgresql.org
Subject: Re: Use select and update together 
In-reply-to: <1316274671.2203.2.camel@localhost.localdomain> 
References: 
 <CAH4qf7Z=q0XVF-2bPL0Ag3mQREUqCLHgJEGcdPP66XMTd6n_7A@mail.gmail.com>
	<CAOWY8=bpkoexYaoqGJf42Fo4Zprj6TwmV6OukKvQkJSEg=Gqxw@mail.gmail.com>
	<4E74B538.8080902@gmx.net>
	<1316274671.2203.2.camel@localhost.localdomain>
Comments: In-reply-to Guillaume Lelarge <guillaume@lelarge.info>
	message dated "Sat, 17 Sep 2011 17:51:10 +0200"
Date: Sat, 17 Sep 2011 12:16:42 -0400
Message-ID: <27584.1316276202@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Guillaume Lelarge <guillaume@lelarge.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