On 4/12/23 2:35 PM, Kirk Wolak wrote:
On Tue, Apr 11, 2023 at 4:38 PM Federico <cfederico87@xxxxxxxxx
A couple of comments. For the more generic, I prefer RETURNING *
you get back all the columns for matching. To me, this solves the
problem in a very generic way.
From what I gather from the conversation RETURNING is the red herring.
The request is that for:
INSERT INTO some_table(char_fld) VALUES('a'), ('b'), ('c')
where some_table has an auto increment field that the values created for
said field will always be done in the order that VALUES data was
presented so:
SELECT id, char_fld from some_table will always return:
(1, 'a')
(2, 'b')
(3, 'c')
But SQL (and SET THEORY) basically imply you cannot trust the sequencing
of a set of transactions. Parallel execution is just a great simple
example.
Secondarily, many frameworks I've worked with (and custom ones
developed) would actually call the SEQUENCE.NEXTVAL, and assign the IDs,
in memory, accepting that we would have gaping holes if some
transactions were never actually sent to the server. We did this a lot
in master-detail GUI type stuff. It's just easier. The children knew
their parent ID, and all the children ID's were effectively known before
committing. It made for simple code that never failed.
(for large datasets we would want one query that returned a set of IDs,
we could order that. And apply it to the records we were about to
insert). [Be Careful with GENERATED ALWAYS pks to OVERRIDE]
HTH
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
