The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/dml-returning.html
Description:
In the docs explaining RETURNING
https://www.postgresql.org/docs/11/dml-returning.html there is no mention of
the fact that a nested sub-select in the RETURNING statement executes on the
table as if the INSERT/UPDATE had not happened.
I suppose maybe this might be obvious if you understand how SQL works but I
think it is nuanced enough that it is worth explaining here as it provides
some useful features for UPSERT queries. Example:
```sql
create table foo (x int primary key, y int);
--=> CREATE TABLE
insert into foo (x, y) values (1, 1);
--=> INSERT 0 1
update foo set y = 2 where x = 1 returning (select y from foo where x = 1)
as old_y;
/* =>
* old_y
* -------
* 1
* (1 row)
*
* UPDATE 1
*/
select * from foo;
/* =>
* x | y
* ---+---
* 1 | 2
* (1 row)
*/
```