Feature Request: DML RETURNING clause

(1) By jimmy (soapboxcicero) on 2020-11-28 04:34:59

There's no standard that I'm aware of but it has support in postgres and mariadb (partial). Oracle and sql server have similar features in their procedural extensions.

Postgres compatibility would be my specific request: [https://www.postgresql.org/docs/13/dml-returning.html]

It doesn't really add any new capabilities but it does cut down on verbosity, especially when using a repl. I would think there are easy optimizations available, however, as the query and manipulation could be done in a single pass.

Apologies if this has been requested before. I couldn't find anything but it's not the most search-friendly term.


Oracle: [https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm]

Sql server: [https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15]

Mariadb:
  *  [https://mariadb.com/kb/en/insertreturning/]
  *  [https://mariadb.com/kb/en/replacereturning/]

(2) By tom (younique) on 2020-12-06 00:04:37 in reply to 1 [link]

As I, too, would love to see this small suggestion being implemented ... any statement by the developers?

(3) By anonymous on 2021-01-24 15:58:40 in reply to 1 [link]

> It doesn't really add any new capabilities but it does cut down on verbosity

That it doesn't add new capabilities is actually somewhat debatable:

* the official documentation indicates that [last_insert_rowid](https://sqlite.org/c3ref/last_insert_rowid.html) is not thread-safe even (I assume even when the connection is)
* because of the `ON CONFLICT` clause, it may be difficult to know what records were affected by an `INSERT` as inserting N records may not lead to the last N records of the table having been inserted, AFAIK there is no way to know which records were inserted and which were ignored or updated

Supporting a `RETURNING` clause would make these scenarios workable.