Re: Proposal: RETURNING primary_key()

Igal, thanks for the analysis.

Craig>Part of the question for Pg is what exactly we should and should not
be returning.

I think the following might be a good starting point: return set of columns
that would identify the inserted row(s).
E.g. columns of any primary key would do. Columns of any unique key would
do as well.
"returning *" would do as well, however it would return excessive columns,
thus it would be less efficient.

I do not think it makes sense to tie "generated keys" to sequences or
things like that.

For example:
1) Consider Pg returns column_name==ABC, value==42. That would mean
client could locate exactly that row via "where ABC=42"
2) Same for multicolumn keys: Pg just returns (col1, col2) == (42, 146).
Then client would be able to locate the row via "where col1=42 and col2=146
3) If multiple unique keys present, it is fine if Pg returns one or the
another depending on the phase of the moon. Yet more compact key would be
preferable to save on bandwidth.

Does that make sense?

I think naming the resulting column(s) like "generated_key" /
"generated_keys" does not make much sense. Especially, for multi-column
keys.

If ctid was update-proof, it could could do. Unfortunately, ctid might
easily get changed.

Theoretical end-to-end (it is the only use of "generated keys" I can
imagine at the moment):
1) Client issues an insert statement, asking "generated keys"
2) Pg inserts the row, and returns resultset with "primary key" (or unique
key) columns.
3) Client stores it somewhere. For instance, passes that to UI.
4) As UI wants to update the row, client just uses those keys to identify
the row to update.

PS. Frankly speaking, I feel "generated keys" is more like a "plug & pray"
kind of API. ORMs should know the column names of the primary keys => ORMs
should use "return specific column names" API, not just "return something
generated".
​
Vladimir