Question on doc for RETURNING clause

Hi, I was thinking of suggesting some doc clarifications and additional examples related to the RETURNING clause. Just a couple 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.html doc 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 contain column 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 result set? 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 * from t1 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 by id 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 or not.

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” inserted column is considered. Here the ordering is by column 2 of the RETURNING clause, which is column 1 from the list of inserted 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 practical effect if there’s no RETURNING clause, e.g. do the rows get physically inserted in the ORDER BY order, which could have implications for columns like SERIAL?

Thanks,
John