From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use select and update together
Date: Sat, 17 Sep 2011 12:16:42 -0400
Message-ID: <27584.1316276202@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, guillaume@lelarge.info, maps.on@gmx.net
Subject: Re: Use select and update together
In-Reply-To: <27584.1316276202@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox