It's recently occurred to me that many people rely on the RETURNING clause for multi-valued INSERTs, INSERT INTO ... SELECT, etc, returning rows in the same order they were supplied as input into the INSERT. I often see lock-step iteration over the inputs to an INSERT and the RETURNING result-tuples used to map generated sequence values to the corresponding input.
I can't find anything relevant in the docs about the order of results in RETURNING, and it strikes me that this should probably be codified one way or the other.
The docs simply read:
> The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT.
Looking at the code I don't see any reason why it wouldn't currently be safe to assume that
INSERT ... VALUES (...), (...), ... RETURNING ...
and
INSERT ... SELECT ... ORDER BY ... RETURNING ...
always return rows in the exact order of their input. But that may not always be true and if it's not documented as safe, it's not necessarily wise to rely on it.
If you can't rely on RETURNING order you have to instead identify a candidate key on the input and use a wider RETURNING result that includes that candidate key as well as whatever else you want returned. Then map the results onto the inputs. No fun at all.
So - I propose to document that INSERT ... RETURNING guarantees that the result-tuples will be in the same order as the input to the INSERT ... RETURNING statement. So if that order is well-defined, as in the case of multi-VALUES clauses or SELECT ... ORDER BY ..., the result order is also well defined.
(An alternative would be adding RETURNING ... WITH ORDINALITY. Which seems pretty horrible).
Thoughts/comments? Barring objections I'll write up a docs patch for the docs list.