SQLite Forum

Feature Request: DML RETURNING clause
Login

Feature Request: DML RETURNING clause

(1) By jimmy (soapboxcicero) on 2020-11-28 04:34:59 [link] [source]

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:

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

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 [source]

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 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.