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