From: | Federico <cfederico87(at)gmail(dot)com> |
---|---|
To: | John Howroyd <jdhowroyd(at)googlemail(dot)com> |
Cc: | Mike Bayer <mike_mp(at)zzzcomputing(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Guidance on INSERT RETURNING order |
Date: | 2023-04-19 07:29:08 |
Message-ID: | CAN19dydZ9Z1vqMsK-AYpEShT-UsuE-zJ4oVeVSXax7k+7=j-Gw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Smith | 2023-04-19 08:27:15 | Re: Support logical replication of DDLs |
Previous Message | Stéphane Dunand | 2023-04-19 07:06:51 | Re: Request for information about postgres version 15.2 stability |