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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
