SQLite Forum

Are there any plans / chances of supporting a RETURNING clause?
I suppose the value of `RETURNING` depends on a good answer to this question: Why would the calling code not already have the desired return values? Didn't it just send them to SQLite to update the DB?

To take the Fossil example, the pattern may be "create new artifact; return artifact ID". Didn't the C code creating that artifact create the new artifact's hash, thus doesn't need SQLite to give it back? It already has the data.

Most of my uses of DB update immediately followed by `SELECT` over the changed data are sufficiently well addressed by `sqlite3_last_insert_rowid()`, since it requires a subsequent trip up through the UI and back down into the app core before I'll know what that subsequent `SELECT` actually is. If I knew what data the user was going to request next, I'd just cache it as part of building the DB update query and return that, not ask SQLite to give me back something I just gave it.

That leads me to wonder if `RETURNING` is largely useful with complicated SQL queries that do calculation on supplied values, so the updated data aren't obvious from the executed query. This requires either use of custom functions or complicated expressions using standard SQLite functions.

Also, doesn't the SQLite caching layer remove a large part of the value of `RETURNING`? As far as I can tell, the main value of this SQL feature is predicated on the idea that a DBMS round-trip is expensive, but that's only true for client/server DBs. If you select from just-modified records, isn't SQLite -- being in-process -- going to return a result from RAM except in the rare instance that another concurrent query modified the same rows in between?

I repeat drh's challenge: what's the use case?