Add support for PostgreSQL returning

[PavelTurk]

It is a very often situation when it is necessary to get some information after INSERT/UPDATE queries. Postgresql supports it via returning . At the same time H2 in PostgreSQL mode doesn't support it.

So, I suggest to add such support so it could be possible to test returning using H2.

[katzyn]

You need to upgrade your version of PostgreSQL to 15 or newer and use the standard MERGE statement instead:
https://h2database.com/html/commands.html#merge_using
https://www.postgresql.org/docs/current/sql-merge.html

[PavelTurk]

@katzyn Thank you for your answer. I am reading now about merge but I don't see what is common between merge and returning. Returning allows to obtain data from modified rows while they are being manipulated. For example, I update rows with where and after executing update query I get ids of the modified rows. Merge - conditionally insert, update, or delete rows of a table.

Could you explain your idea?

[katzyn]

Sorry, I thought about ON CONFLICT clause by mistake.

The SQL Standard has more powerful data change delta tables for a long time and H2 supports them well enough:
https://h2database.com/html/grammar.html#data_change_delta_table

So you should ask developers of PostgreSQL about this standard feature, they definitely have more resources than us.

[katzyn]

For now, you can use Statement.getGeneratedKeys() in JDBC, this method is supported by both PgJDBC and H2 drivers (and by many other drivers too).

To use this method you need to pass Statement.RETURN_GENERATED_KEYS, column names, or their ordinal indexes first, otherwise they will not be collected.

[PavelTurk]

Here they say that posgresql returning is equivalent of data_change_delta_table. So, what should I do with this issue? I don't think that psql will support data_change_delta_table as they have their own solution. Should H2 support their non standard solution in PostgreSQL mode?

@katzyn What will you say? Should this issue be closed or not?

[katzyn]

RETURNING provides only a subset of this functionality, data change delta tables are more powerful.

This feature request doesn't need to be closed, but if bugtracker of PostgreSQL doesn't have a feature request about the standard syntax, such feature request needs to be created, it is more reasonable to implement standard features everywhere.

Vendor-specific features are hard to implement (because their documentation of database systems usually doesn't describe an expected behavior in various special cases) and their usage only makes your SQL less portable across various database engines.

[PavelTurk]

@katzyn I created "issue" in PostgreSQL mailing list - see https://www.postgresql.org/message-id/b85900eb-9e3c-e358-aa8f-5a27b30c17e7%40gmail.com . Let's see what they will say.

[PavelTurk]

@katzyn I got answer in another postgresql mailing list. See https://www.postgresql.org/message-id/flat/4f12dcdb-6d17-22a9-14e7-c006c97f5fab%40gmail.com