Re: Use select and update together - Mailing list pgsql-sql

From Guillaume Lelarge
Subject Re: Use select and update together
Date
Msg-id 1316274671.2203.2.camel@localhost.localdomain
Whole thread Raw
In response to Re: Use select and update together  (Andreas <maps.on@gmx.net>)
Responses Re: Use select and update together
List pgsql-sql
On Sat, 2011-09-17 at 16:56 +0200, Andreas wrote:
> Am 13.09.2011 07:50, schrieb pasman pasmański:
> > In 8.4 this syntax is not implemented.
> 
> 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;


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com



pgsql-sql by date:

Previous
From: Josh Kupershmidt
Date:
Subject: Re: insert or update within transaction
Next
From: Tom Lane
Date:
Subject: Re: Use select and update together