Re: Guidance on INSERT RETURNING order

PS: Sorry, I haven't yet thought how this might work with UPDATE or MERGE,
but if I am on the right track with INSERT I'll give this some thought.

On Mon, 17 Apr 2023 at 18:48, John Howroyd <jdhowroyd(at)googlemail(dot)com> wrote:

> 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!
>