Proposal: RETURNING primary_key()

THE ISSUE:

In JDBC there is a flag called RETURN_GENERATED_KEYS --
https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#RETURN_GENERATED_KEYS

Which is left quite ambiguous, but in general it is used to return the
"generated" Primary Key on INSERT/UPDATE/DELETE -- which is mostly
useful in the case of INSERT, of course, as the other commands do not
generate a key.

We can already add RETURNING after an INSERT, e.g.

CREATE TABLE test (name TEXT, id SERIAL PRIMARY KEY);
INSERT INTO test VALUES ('PostgresQL') RETURNING id;

But the problem is that we need to know in advance the name of the "id"
column, because if we had created the table like so:

CREATE TABLE test (name TEXT, test_id SERIAL PRIMARY KEY);

Then we would need to use RETURNING "test_id" instead of "id".

The problem is that we do not always know in advance what the Primary
Key is, and therefore a solution that was implemented in the pgjdbc
driver was to append " RETURNING * " to the query, but that has its own
problems, like returning a lot of data that is not needed, etc. (you
can see a longer discussion at https://github.com/pgjdbc/pgjdbc/issues/488 )

THE PROPOSAL:

The proposal is to allow something like RETURNING primary_key() (it can
be a keyword, not necessarily a function), e.g.

INSERT INTO test VALUES ('PostgresQL') RETURNING primary_key();

Which will return a record set according to the PRIMARY KEY that is set
on the table. So if the primary is "id", then you would get a column
named "id", and if it is "test_id" you would get a column named
"test_id" with the correct values.

If the PRIMARY KEY is made of multiple column, then all of those columns
will be returned.

If the table does not have a PRIMARY KEY constraint then NULL will be
returned with some arbitrary column name.

I would go further and suggest to add a function that will return the
last primary key from a table, e.g.:

SELECT last_primary_key() FROM test;

This of course can be beneficial for many users, and not only the JDBC
community.

Thank you for your time and consideration,

Igal