Skip site navigation (1) Skip section navigation (2)

Cannot select from 'UPDATE RETURNING'

From: valgog <valgog(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Cannot select from 'UPDATE RETURNING'
Date: 2007-09-12 14:41:31
Message-ID: 1189608091.469882.236560@22g2000hsm.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

I am no sure if it is a bug at all, but according to the documented
features of UPDATE I would suppose it is.

UPDATE RETURNING clause cannot be used in SELECT * FROM ( query ) AS
query_alias statement.

For example:

update issues set issue_value = 'value to assign' where id in ( <some
ids> ) returning id, last_update

Is valid and compiles and is executed without any problems. It is
actually returning IDs and last update timestamps of the updated
records of the ITEMS table.

BUT

SELECT * FROM ( update issues set issue_value = 'value to assign'
where id in ( <some ids> ) returning id, last_update ) as
update_results

does not even compile and throws the following error:

ERROR:  syntax error at or near "set"
LINE 1: select * from ( update issues set issue_value = 'v...
                                               ^

********** Error **********

ERROR: syntax error at or near "set"
SQL state: 42601
Character: 40

The real example is much more complicated with not such a trivial
update and with the outer SELECT calculating count and max of the
last_update...

By now I have to run it in a PL/SQL FOR .. IN LOOP to calculate the
results, but it would be nice to have the RETURNING clause documented
better.

With best regards,

-- Valentine Gogichashvili


pgsql-bugs by date

Next:From: St├ęphane SchildknechtDate: 2007-09-13 06:39:51
Subject: CREATE USER and createuser not working the same
Previous:From: Marshall, SteveDate: 2007-09-12 12:25:25
Subject: PL/TCL can make postgres become multithreaded

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group