From: | John Howroyd <jdhowroyd(at)googlemail(dot)com> |
---|---|
To: | Mike Bayer <mike_mp(at)zzzcomputing(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Guidance on INSERT RETURNING order |
Date: | 2023-04-17 17:48:36 |
Message-ID: | CAAGaYByweMY8iF8eZm4fw-1cFh9BjC8Zu63PQOoBUPh_zCt3EQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
May I clarify the ideas being discussed so far, perhaps with a view to make
a relevant proposal. My apologies if I get anything 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 associated to ordinality =
1) and table.id = 1003 was assigned to the inserted row from tuple
(4,5,6). The output being ordered as determined by the internals of query
execution (not necessarily the one shown).
Is that correct?
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 by the ORDER BY clause'; so pretty
much like a row_number() but in the output of the RETURNING clause (and
without an OVER modification). 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 ordinality output).
Is that correct?
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 declaration for 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 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.
Apologies, if I have misunderstood or invented something that's not
possible!
From | Date | Subject | |
---|---|---|---|
Next Message | John Howroyd | 2023-04-17 18:03:27 | Re: Guidance on INSERT RETURNING order |
Previous Message | Laurenz Albe | 2023-04-17 15:38:34 | Re: Should I add a new schema for my application? |