SQLite Forum

Some comments about RETURNING
Login

Some comments about RETURNING

(1) By anonymous on 2021-02-10 03:01:10 [source]

I think that RETURNING should not need to be implemented in CTEs and subqueries.

However, I do think that diverting the returning into another table should be implemented. I don't know what PostgreSQL does, although if PostgreSQL doesn't already have a syntax for that (or its syntax is no good), then I can suggest:

RETURNING data_list [INTO [schema_name.]table_or_view[(column_list)] [returning_clause]]

You could also return into a view with one or more INSTEAD OF INSERT triggers, or a virtual table, in addition to ordinary tables.

If you use RETURNING INTO then you could chain them by adding more RETURNING clauses, including to return them in the result set too if you want to do. (Although, maybe chaining is not a high priority; perhaps we don't really need that.)

Also, as it turns out, for a program I was writing, supporting RETURNING for DELETE on virtual tables (at least for those that have a rowid) would be a useful thing to have.

(2) By Larry Brasfield (larrybr) on 2021-02-10 12:45:06 in reply to 1 [link] [source]

However, I do think that diverting the returning into another table should be implemented. I don't know what PostgreSQL does, although if PostgreSQL doesn't already have a syntax for that (or its syntax is no good), then I can suggest: ...

PostgreSQL has this syntax for using DML with a RETURN clause ("DMLR"): WITH some_name AS <DML with a RETURN clause> <query or DML using some_name as a table-like object>

With the capability indicated by that syntax, the DMLR results can be captured in a temp table if they are to be used for successive operations. I think this reduces the attraction of adding more complex syntax to enable "chaining" because it is a simple matter to use a temp table then drop it. An implementation might even optimize for that case by simply collecting the DMLR results directly into a named temp table.

(No comment on DMLR with virtual tables.)