Re: Guidance on INSERT RETURNING order

On Tue, 18 Apr 2023 at 11:53, John Howroyd <jdhowroyd(at)googlemail(dot)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(at)gmail(dot)com> wrote:
>>
>> On Tue, 18 Apr 2023 at 00:21, John Howroyd <jdhowroyd(at)googlemail(dot)com> wrote:
>> > ...
>> >
>> > 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.
>>
>> 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 envisage a system to handle this, but at (presumably) much greater expense: preparation of the SELECT, orchestration of the output rows (some fields for INSERT some for RETURNING) and (presumably) a whole load of mem copies to RETURNING. Is this 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 presumably expensive).

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