PostgreSQL using Update in Select;

Question

PostgreSQL

PostgreSQL since version 8 supports the RETURNING keyword in UPDATE queries.

Why can't a SELECT be made from the results returned by an UPDATE?

Example:
Preparation: Check: WTF!!!

CREATE TABLE tbl( KEY int, val int);
Insert into tbl values (1,1);

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


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

ERROR: syntax error at or near "SET"
СТРОКА 2: UPDATE tbl SET val = 1

Answer 1

thanks for the help. The answer was found on Stackoverflow - it's just a PG bug.
stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres

Answer 2

If you just need to get data, read the result of the update like a normal select.

Answer 3

Yesterday a new version was released - 9.1, it should now work. At least it's stated like this:

* Data modification (INSERT/UPDATE/DELETE) is now allowed in the WITH clause.

Answer 4

... RETURNING*;