RETURNING does not explain evaluation context for subqueries

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: deonjtan(at)gmail(dot)com
Subject: RETURNING does not explain evaluation context for subqueries
Date: 2020-02-05 16:32:45
Message-ID: 158092036515.1089.2611167368836790265@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-hackers

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)
*/
```

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Stephen Frost 2020-02-05 18:30:05 Re: Users/Roles do not align.
Previous Message PG Doc comments form 2020-02-05 09:36:49 Wrong insert before trigger examples

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2020-02-05 16:37:30 Re: Index Skip Scan
Previous Message Fabien COELHO 2020-02-05 16:31:38 Re: Add %x to PROMPT1 and PROMPT2