Re: Proposal: RETURNING primary_key()

On 3/7/2016 1:20 PM, Tom Lane wrote:
>
> Yeah. I'm rather suspicious of this proposal; I do not think it's
> actually very useful to return a primary-key value without any indication
> of what the primary key is. There are also corner cases where it seems
> pretty ill-defined. For example, suppose you do this on an inheritance
> parent table that has a pkey defined, but not all its child tables do
> (or maybe they do but their pkeys aren't identical to the parent's).
> What should happen then?
First, thank you for your reply. I appreciate it. I do not know the
answer to that question.
>
> That's an exceptionally weak use-case to argue for this with. Unless
> you can get *all* those DBMS suppliers to invent equivalent features,
> you're going to have to have pkey-querying logic anyway. The argument
> for bespoke syntax for it in just one DBMS seems pretty weak.
Fair enough, but my idea was that this will be used by the JDBC driver
in this case. The other DBMS suppliers have their JDBC driver return a
value, usually it is SERIAL type. But there is no standard for the
column name. In SQL Server, for example, it is IDENTITYCOL while in
MySQL it is GENERATED_KEY.

The thing is that in SQL Server I can do, for example, "SELECT
@@identity" and get the last value that was inserted. In SQL Server,
however, Microsoft took the easy way and enforced only a single
auto-generated identity column per table. The closest thing I can do in
PostgreSQL is "SELECT lastval()" but what if there are multiple
sequences in that table?
> I am fairly sure, also, that all of those systems have support for the
> SQL-standard information_schema views. So if you write a pkey-identifying
> query against those views, you'd have some chance of a solution that
> actually did work everywhere.
The other JDBC drivers return the last SERIAL value from the table that
had the insert, so there's no issue there. Querying the
information_schema views with each INSERT will probably cause a major
performance hit.

Anyway, I trust that you know much more about databases than I do, so if
you don't think that it's a good idea, I accept that.

Best,

Igal