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
