Postgres Pro
Downloads
Home   >   mailing lists

Thread: Use select and update together

Use select and update together

From
Boris
Date:
Hi, this is my first on this list.

I can't understand where my problem in PG 8.4.

CREATE TABLE tbl( KEY  int, val int);

Update query like:

UPDATE tbl SET val = 1            WHERE KEY = any('{0,1,2,3,4,5}'::int[])        returning key;

work well. But any try to wrap it in select query like:

SELECT (        UPDATE tbl SET val = 1            WHERE KEY = any('{0,1,2,3,4,5}'::int[])        returning key
);

cause syntax error. Is any query of such type (update warped into
select) is possible?


Best regards, Kuzikov Borys

Re: Use select and update together

From
pasman pasmański
Date:
In 8.4 this syntax is not implemented.

2011/9/12, Boris <kuzikoff@ukr.net>:
> Hi, this is my first on this list.
>
> I can't understand where my problem in PG 8.4.
>
> CREATE TABLE tbl( KEY  int, val int);
>
> Update query like:
>
> UPDATE tbl SET val = 1
>              WHERE KEY = any('{0,1,2,3,4,5}'::int[])
>          returning key;
>
> work well. But any try to wrap it in select query like:
>
> SELECT (
>          UPDATE tbl SET val = 1
>              WHERE KEY = any('{0,1,2,3,4,5}'::int[])
>          returning key
> );
>
> cause syntax error. Is any query of such type (update warped into
> select) is possible?
>
>
> Best regards, Kuzikov Borys
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


-- 
------------
pasman

Re: Use select and update together

From
Samuel Gendler
Date:


On Mon, Sep 12, 2011 at 3:36 AM, Boris <kuzikoff@ukr.net> wrote:

SELECT (
        UPDATE tbl SET val = 1
            WHERE KEY = any('{0,1,2,3,4,5}'::int[])
        returning key
);

cause syntax error. Is any query of such type (update warped into
select) is possible?


Just lose the select (); part.

update tbl set val = 1 where condition = true returning *;

That works just fine on 8.4.  You can also specify individual columns in the returning clause.  The postgresql documentation covers the topic.


It doesn't appear to work in a subquery, so you can't join to it (though you can use a join within the update clause and then return the columns you need from a joined table, I suspect).  Simply returning 1 or more columns from updated rows works just fine, though.

Re: Use select and update together

From
Andreas
Date:
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.

Re: Use select and update together

From
Guillaume Lelarge
Date:
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

Re: Use select and update together

From
Tom Lane
Date:
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