SQLite Forum

Use of RETURNING clause as a subquery

> DML statements cannot be used in subqueries. I suppose that should be mentioned in the documentation.

Perhaps it should be mentioned. The syntax railroad diagrams "mention" that fact by omission, but deducing that limitation requires a careful study.

If something looks like a duck and acts like a duck in some way(s) but not all, an explicit caution, "This is NOT a duck.", is a favor to readers.

On behavior rather than documentation:

My thinking, and reason for liking the RETURNING clause feature (before understanding that limitation), was that it would simplify pure SQL operations that would otherwise require repetition of SELECT queries or client code written to collect a SELECT result set then run the DML.

When using SQL, it is helpful for comprehension that a category of "table-valued expressions" exists. Table and view names, table-valued functions, and SELECT queries can all be used as a "row set" within containing SQL. I had thought that DML with a RETURNING clause would and should also function as a table-valued expression. However, that simple view ignores an important side effect.

Of course, DML with RETURNING ("DMLR" here) is not something that should be expected to have the same "value" when repeated within an expression. I suspect this is why PG makes a DMLR table-value only usable when it is part of a WITH query. The WITH syntax makes avoiding repetition convenient and expresses reuse.

Even if a WITH-DMLR expression can only be followed by a single DML operation, it is useful for simple things like adding to an audit log. Follow-on operations that require multiple DML statements can be effected by a "WITH DMLR store-into-temp-table; use temp-table for more DML" sequence.

If the DMLR "value" could be used after a WITH ... query, using a secret temp table in the implementation would normally be a reasonably performant approach. The alternative involves storage of the DMLR result set anyway, within client code or with the repeated query returning what is to be modified.