Returning clause support

[beikov]

Many DBs have some kind of RETURNING clause that allows to specify which columns of inserted/updated/deleted rows should be returned from a DML statement. We could also use this mechanism internally for fetching generated values after entity inserts/updates instead of doing full fetches.

I propose a syntax extension for DML statements: ('returning' expression (',' expression)*)?

Emulating this feature, although not easy, should be possible. We'd have to use our multi-table facilities for that purpose, which will first insert ids of entities to modify into a temporary table, then run the DML and finally run a select statement for the returning clause based on the data from the temporary table.

[gavinking]

We could also use this mechanism internally for fetching generated values after entity inserts/updates instead of doing full fetches

We already do this, don't we?

[beikov]

We currently always run a dedicated select statement for generated properties (see GeneratedValuesProcessor), which we could already avoid, because the SQL AST already models this returning clause. The note about using this internally was only tangential though. My main motivation for this discussion is to discuss support for this clause in HQL/Criteria.