On Fri, Apr 14, 2023, at 11:17 PM, Tom Lane wrote:
> Federico <cfederic...@gmail.com> writes:
>> Would something like what was proposed by Mike Bayer be considered?
>
>>> A new token called "tuple_order" or something
>>>
>>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING
>>> table.id, inserted.tuple_order
>>>
>>> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate
>>> the each row delivered by RETURNING to each entry in the VALUES clause, in
>>> the order they were stated in that VALUES clause, that is entry (1, 2, 3)
>>> would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, etc.
>
> As proposed, I don't think so. Something over in the RETURNING clause has
> exactly no connection to VALUES. What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?
in my "plan", the token would not be supported and an error would be raised.
>
> There is some prior art in this area, though. See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM. It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.
I appreciate this idea! Any kind of keyword / syntax that frees us from
having to round-trip additional data to the database and/or generate more
complex syntaxes for certain kinds of default generation schemes would simplify
the approach.
