Re: "RETURNING PRIMARY KEY" syntax extension

On 4 July 2014 11:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Sure it can - it append RETURNING PRIMARY KEY and hand back a ResultSet
> > from whatever was returned. It's CURRENTLY doing that, but it's appending
> > RETURNING * and leaving it up to the caller of getGeneratedKeys() to work
> > out which columns the caller is interested in.
>
> I might be mistaken, but as I read the spec for getGeneratedKeys, whether
> or not a column is in a primary key has got nothing to do with whether
> that column should be returned. It looks to me like what you're supposed
> to return is columns with computed default values, eg, serial columns.
> (Whether we should define it as being *exactly* columns created by the
> SERIAL macro is an interesting question, but for sure those ought to be
> included.) Now, the pkey might be a serial column ... but it equally
> well might not be; it might not have any default expression at all.
> And there certainly could be serial columns that weren't in the pkey.
>
> The fact that the spec is kinda fuzzy doesn't entitle us to ignore the
> plain meaning of the term "generated key".
>

Well, strictly speaking no, but in reality it doesn't look like other
databases that support this feature support anything other than returning
auto-increment fields on primary keys, often only supporting a single
column (see [1]). Oracle doesn't support even that - callers have to call
the variant that specifies a column list, since historically there was no
real support for recognising such columns, oracle being sequence based.

As such, there can't be any callers in the wild that will expect this to
return anything other than the primary key, because there's no support for
doing anything else. And if the caller really DOES want other columns
returned, they can always call the variant that allows them to specify
those columns, or just issue a normal query with a returning clause. This
feature really exists to support the default case where those columns
aren't specified by the caller, and given current driver support (and
sanity) the only thing any caller will expect from such a result is the
primary key.

Cheers

Tom

[1]
http://www.postgresql.org/message-id/CAPPfruwQY0Z66TRv4XmDQnyv0PrJkY+38x+P4VKhMRrw5rbPAQ@mail.gmail.com