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

On Tue, 18 Apr 2023 at 00:21, John Howroyd <jdhowroyd@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
getanything 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
associatedto ordinality = 1) and table.id = 1003 was assigned to the inserted row  from tuple (4,5,6).  The output
beingordered as determined by the internals of query execution (not necessarily the one shown). 
>
> Is that correct?

That would work as syntax for the task of tracking what id or other
server default is generated by a value clause tuple.

> 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
bythe ORDER BY clause'; so pretty much like a row_number() but in the output of the RETURNING clause (and without an
OVERmodification).  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
ordinalityoutput). 
>
> Is that correct?

This would not be needed if the syntax with VALUES WITH ORDINALITY is
added in sqlalchemy. So fine either way.
If "WITH ORDINALITY" is a feature of VALUES this syntax would not be
allowed though. I'm personally ok limiting WITH ORDINALITY only to
VALUES.

> 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
declarationfor 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
valuesin the RETURNING clause (namely, v.num from a subexpression of the SELECT but in whatever order it comes).  On
theother hand, that seems a lot more complicated to me because it is not an expression in the overall SELECT feeding
theINSERT, whereas the WITH ORDINALITY is a specific declaration to match input order with output order by inserting a
counter.

I didn't mean to suggest any particular order should be kept by insert
or by returning. I was merely commenting on the David G. Johnston
reply

     I suppose breaking the restriction that only columns present on
the insertion-table can be returned is a possible option that also
solves another infrequent request.

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

Thanks for the recap. I'm hoping this can become a proposal.

Best,
  Federico