JetBrains / Exposed

Kotlin SQL Framework
http://jetbrains.github.io/Exposed/
Apache License 2.0
8.05k stars 674 forks source link

feat: EXPOSED-355 Support INSERT...RETURNING statement #2060

Closed bog-walk closed 2 months ago

bog-walk commented 2 months ago

Add support for RETURNING clause with insert and upsert statements, in PostgreSQL and SQLite.

This PR sets the base for supporting the same clause with update and delete statements. So more tests will follow with those implementations.

Usage: By default, the returned data is set to all columns from the invoking table.

val result1: List<ResultRow> = Items.insertReturning {
    it[name] = "A"
    it[price] = 99.0
}

Items.insertReturning(listOf(Items.id, Items.name)) {
    it[name] = "B"
    it[price] = 200.0
}.map {
    it[Items.id].value to it[Items.name]
}

Note: The statement auto-executes, just like other insert/update/delete, and immediately iterates over (and closes) the ResultSet to populate a List<ResultRow> that is returned. I thought this was the best behavior to follow the pattern of statements.

But this may be undesired if users are returning result sets with large amounts of records. If so, it would be no issue to have the statement extend Iterable, like Query or ExplainQuery, but this would mean it does not get executed until iterated over.

To-do:

Note: Oracle support is not included because it requires a RETURNING ... INTO local_variable clause. All examples show the _localvariable and command being run using PL/SQL in a DECLARE ... BEGIN ... END; block.