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