Question on doc for RETURNING clause - Mailing list pgsql-docs

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