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 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).