Thread: Cannot select from 'UPDATE RETURNING'

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