Currently, DML RETURNING only supports singleton inserts/updates/deletes. This restriction should be relaxed so that the following statements can return multiple rows:
INSERT ... SELECT ...- Searched
UPDATE
- Searched
DELETE
MERGEUPDATE OR INSERT
The following can remain as-is (though maybe a unified implementation makes more sense?):
INSERT ... DEFAULT VALUES / INSERT ... VALUES (...) (at least, until Firebird supports table value constructors)- Positioned
UPDATE (with WHERE CURRENT OF ...)
- Positioned
DELETE (with WHERE CURRENT OF ...)
I suggest the implementation describes itself as isc_info_sql_stmt_select; this should make it compatible with most libraries and tools that rely on the statement type to decide on how to execute. The alternative is to introduce a new type (or types), for example isc_info_sql_stmt_dml_returning or something, but I think that makes things harder because tools and libraries would need to be updated to support this.
When such a statement is executed, Firebird should execute the statement to completion and collect all requested data in a type of temporary table, once execution is complete, fetches are done against this temporary table. In other words, even if no rows are fetched, all DML changes have been performed.
In PSQL, these statements should use the current singleton behaviour.
Non-singleton FOR ... RETURNING in PSQL is tracked in #6925.
Currently, DML RETURNING only supports singleton inserts/updates/deletes. This restriction should be relaxed so that the following statements can return multiple rows:
INSERT ... SELECT ...UPDATEDELETEMERGEUPDATE OR INSERTThe following can remain as-is (though maybe a unified implementation makes more sense?):
INSERT ... DEFAULT VALUES/INSERT ... VALUES (...)(at least, until Firebird supports table value constructors)UPDATE(withWHERE CURRENT OF ...)DELETE(withWHERE CURRENT OF ...)I suggest the implementation describes itself as
isc_info_sql_stmt_select; this should make it compatible with most libraries and tools that rely on the statement type to decide on how to execute. The alternative is to introduce a new type (or types), for exampleisc_info_sql_stmt_dml_returningor something, but I think that makes things harder because tools and libraries would need to be updated to support this.When such a statement is executed, Firebird should execute the statement to completion and collect all requested data in a type of temporary table, once execution is complete, fetches are done against this temporary table. In other words, even if no rows are fetched, all DML changes have been performed.
In PSQL, these statements should use the current singleton behaviour.
Non-singleton
FOR ... RETURNINGin PSQL is tracked in #6925.