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 <jdhowr...@googlemail.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! >
