Re: Guidance on INSERT RETURNING order - Mailing list pgsql-general

May I clarify the ideas being discussed so far, perhaps with a view to make a relevant proposal.  My apologies if I get anything wrong or go too far.

As I understand it the proposal is to supplement the syntax to something like:

INSERT INTO table (a, b, c)
VALUES ((1,2,3), (4,5,6), ...)
WITH ORDINALITY
RETURNING table.id, ordinality
;

The meaning of which is to adjoin an ordinality column to the output reflecting the declaration order in the values clause.  So an output of (not necessarily in any order):
(1001, 1)
(1003, 2)
means that table.id = 1001 was assigned to the inserted row from tuple (1,2,3) (from VALUES, because that table.id is associated to ordinality = 1) and table.id = 1003 was assigned to the inserted row  from tuple (4,5,6).  The output being ordered as determined by the internals of query execution (not necessarily the one shown).

Is that correct?

I presume (although, not quite so clear) that one would have:

INSERT INTO table (a, b, c)
SELECT a_val, b_val, c_val
FROM joined_tables
WHERE some_condition
ORDER BY something_relevant
WITH ORDINALITY
RETURNING table.id, ordinality
;

The meaning being very much as before replacing 'declaration order' by 'row order of the SELECT statement as defined by the ORDER BY clause'; so pretty much like a row_number() but in the output of the RETURNING clause (and without an OVER modification).  I added the ORDER BY clause as I don't really see what this would mean without it; but this (presumably) does not affect output order only the order of the incoming rows (and hence the generation of the ordinality output).

Is that correct?

Might there be a natural syntax to label the 'ordinality' output column?  Perhaps something like:

...
WITH ORDINALITY (col_name)
RETURNING table.id, col_name
;

I don't want to clash with the syntax for Table Functions.

Is it a step too far to propose allowing an additional ORDER BY clause after the RETURNING clause (a specific declaration for the query execution to assign cpu cycles; especially if the WITH ORDINALITY is not tied to output order)?

Personally, I didn't see Frederico's comment as anything to do with order; just how one could output additional values in the RETURNING clause (namely, v.num from a subexpression of the SELECT but in whatever order it comes).  On the other hand, that seems a lot more complicated to me because it is not an expression in the overall SELECT feeding the INSERT, whereas the WITH ORDINALITY is a specific declaration to match input order with output order by inserting a counter.

Apologies, if I have misunderstood or invented something that's not possible!