In JDBC there is a flag called 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 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
Thank you for your time and consideration,
Sent via pgsql-hackers mailing list (email@example.com)
To make changes to your subscription: