Re: Guidance on INSERT RETURNING order

On Tue, 18 Apr 2023 at 11:53, John Howroyd <jdhowroyd@googlemail.com> wrote:
>
> Sorry, I may have jumped to a conclusion that's not quite correct.

Not at all, thanks for moving this along

> On Mon, 17 Apr 2023 at 23:58, Federico <cfederico87@gmail.com> wrote:
>>
>> On Tue, 18 Apr 2023 at 00:21, John Howroyd <jdhowroyd@googlemail.com> wrote:
>> > ...
>> >
>> > 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.
>>
>>
>> ...
>> Best,
>>   Federico
>
>
> This might be a possibility.  The v.num (from the original example) is accessible in the outer select, so one can a
envisagea system to handle this, but at (presumably) much greater expense: preparation of the SELECT, orchestration of
theoutput rows (some fields for INSERT some for RETURNING) and (presumably) a whole load of mem copies to RETURNING.
Isthis something to consider (perhaps just for feasibility while writing an initial patch based on WITH ORDINALITY)? 
>
> To put it another way, v.num is a valid expression in the overall SELECT, but still a lot more complicated (and
presumablyexpensive). 

I think it's probably best to focus only on INSERT VALUES WITH
ORDINALITY. Further enhancements can be added later if they seem
useful.

Best,
  Federico