Обсуждение: Question on doc for RETURNING clause

Hi, I was thinking of suggesting some doc clarifications and additional examples related to the RETURNING clause. Just
acouple of questions first to see if my understanding is correct.
 

There’s the basic usage of the RETURNING clause, like is shown on the
https://www.postgresql.org/docs/current/dml-returning.htmldoc page:
 

```
postgres=> create table ret (id serial, x int, s varchar);
CREATE TABLE
postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s;
 id |   s   
----+-------
  1 | one
  2 | two
  3 | three
```

> The allowed contents of a RETURNING clause are the same as a SELECT command's output list (see Section 7.3). It can
containcolumn names of the command's target table, or value expressions using those columns.
 

I was trying to figure out what the precise “thing” is that comes back from a RETURNING clause. A table reference? A
resultset? The glossary mentions it in the context of result sets:
 

https://www.postgresql.org/docs/16/glossary.html#GLOSSARY-RESULT-SET

That made me think both a RETURNING clause could work in contexts such as CTE (yes) and subquery (seems like no).

A DML statement with a RETURNING clause can be used in a CTE:

```
postgres=> with t1 as (insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s) select *
fromt1 order by id desc;
 
 id |   s 
----+------- 
  6 | three
  5 | two
  4 | one
```

But it can’t be used in a subquery:

```
postgres=> select * from (insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s) t1 order
byid desc;
 
ERROR:  syntax error at or near "into"                       
LINE 1: select * from (insert into ret (x, s) values (1, 'one'), (2,...
```

I couldn’t tell from the definition of subqueries in 7.2.1.3 if a DML with a RETURNING clause should be allowed there
ornot.
 

INSERT/UPDATE/DELETE are mentioned in the with_query block of the SELECT statement:
https://www.postgresql.org/docs/16/sql-select.html

Also on that page:

> PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in the SQL standard.

Is MERGE allowed in that ^^^ context?

Having a RETURNING clause doesn’t magically make a DML statement recognize extra clauses like ORDER BY:

```
postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') returning id, s order by id desc;
ERROR:  syntax error at or near "order"
LINE 1: ... 'one'), (2, 'two'), (3, 'three') returning id, s order by i...
                                                             ^
```

Although intriguingly ORDER BY was recognized if I put it before RETURNING:

```
postgres=> insert into ret (x, s) values (1, 'one'), (2, 'two'), (3, 'three') order by id desc returning id, s;
ERROR:  column "id" does not exist
LINE 1: ...ues (1, 'one'), (2, 'two'), (3, 'three') order by id desc re...
                                                             ^
HINT:  There is a column named "id" in table "ret", but it cannot be referenced from this part of the query.
```

If I didn’t rely on the column name, I could do ORDER BY as part of the INSERT… but it seems like only the “real”
insertedcolumn is considered. Here the ordering is by column 2 of the RETURNING clause, which is column 1 from the list
ofinserted columns:
 

```
postgres=> insert into generatedfields (x) values (0), (10), (100) order by 1 desc returning id, x;
 id |  x  
----+-----
 13 | 100
 14 |  10
 15 |   0                                                      
```

The statement is only aware of 1 column that it can order by, not 2 as in the RETURNING clause:

```
postgres=> insert into generatedfields (x) values (0), (10), (100) order by 2 desc returning id, x;
ERROR:  ORDER BY position 2 is not in select list
LINE 1: ...eratedfields (x) values (0), (10), (100) order by 2 desc ret...
                                                             ^
```

Is the acceptance of ORDER BY documented anywhere? I didn’t see that anywhere in the INSERT syntax. Does it have any
practicaleffect if there’s no RETURNING clause, e.g. do the rows get physically inserted in the ORDER BY order, which
couldhave implications for columns like SERIAL?
 

Thanks,
John

"Russell, John" <johrss@amazon.com> writes:
> Hi, I was thinking of suggesting some doc clarifications and additional examples related to the RETURNING clause.
Justa couple of questions first to see if my understanding is correct. 

> I was trying to figure out what the precise “thing” is that comes back from a RETURNING clause. A table reference? A
resultset? 

I'd say it's a result set, just like the output of SELECT.

> That made me think both a RETURNING clause could work in contexts such as CTE (yes) and subquery (seems like no).

We disallow DML in subqueries because there's a lot of squishiness
around when a subquery is evaluated, whether it's evaluated to
completion, or indeed whether it's evaluated more than once.
CTEs have tighter semantics and so it's practical to require
"exactly once" evaluation for CTEs.  Partly this is a matter of
historical expectations, but I doubt we'd consider revisiting it.

>> PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in the SQL standard.

> Is MERGE allowed in that ^^^ context?

Not yet, as you'd find out if you tried it.  I think there's a patch
in the pipeline to allow it.

            regards, tom lane

> On Jan 11, 2024, at 12:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> "Russell, John" <johrss@amazon.com> writes:
>> Hi, I was thinking of suggesting some doc clarifications and additional examples related to the RETURNING clause.
Justa couple of questions first to see if my understanding is correct.
 
> 
>> I was trying to figure out what the precise “thing” is that comes back from a RETURNING clause. A table reference? A
resultset?
 
> 
> I'd say it's a result set, just like the output of SELECT.
> 
>> That made me think both a RETURNING clause could work in contexts such as CTE (yes) and subquery (seems like no).
> 
> We disallow DML in subqueries because there's a lot of squishiness
> around when a subquery is evaluated, whether it's evaluated to
> completion, or indeed whether it's evaluated more than once.
> CTEs have tighter semantics and so it's practical to require
> "exactly once" evaluation for CTEs.  Partly this is a matter of
> historical expectations, but I doubt we'd consider revisiting it.

Makes sense. I don’t mind the limitation, I was just thinking of places to document it and examples to add to
illustrateusage of RETURNING. If you want to rename columns in the result set, use AS clauses for the column names in
RETURNING.If you want to sort and filter what comes back from RETURNING, use a CTE and select from that. My first idea
wasto try a subquery for all such cases; hadn’t thought of how subqueries might get pruned, moved around, or repeated.
 

>>> PostgreSQL allows INSERT, UPDATE, and DELETE to be used as WITH queries. This is not found in the SQL standard.
> 
>> Is MERGE allowed in that ^^^ context?
> 
> Not yet, as you'd find out if you tried it.  I think there's a patch
> in the pipeline to allow it.

On the way to trying it, I did see in the doc that MERGE doesn’t have a RETURNING clause, which I presume makes the
pointmoot. If there’s a patch that adds such support in process, I won’t suggest adding “MERGE not supported” at this
spotin the doc.
 

Thanks,
John