From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: deonjtan@gmail.com
Subject: RETURNING does not explain evaluation context for subqueries
Date: Wed, 05 Feb 2020 16:32:45 +0000
Message-ID: <158092036515.1089.2611167368836790265@wrigleys.postgresql.org> (raw)
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)
*/
```
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, deonjtan@gmail.com
Subject: Re: RETURNING does not explain evaluation context for subqueries
In-Reply-To: <158092036515.1089.2611167368836790265@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox