SQLite Forum

Use of RETURNING clause as a subquery
Using this table,<code>
</code>, (lifted from [RETURNING](https://www.sqlite.org/draft/lang_returning.html) ), and having another table,<code>
  create table t0log (whenCreated text, what integer);
</code>, I am trying to insert into t0log the results returned by this INSERT statement,<code>
 INSERT INTO t0(c) VALUES(random()) RETURNING b,c;
</code>. The fact that DML with a RETURNING clause produces results, just as select statements do, leads me to believe that inserting them into t0log should be possible. This [page on PostgreSQL SELECT](https://www.postgresql.org/docs/current/sql-select.html) substantiates my notion that DML with a RETURNING clause may be used as a subquery (in PostgreSQL at least. See the with_query use and expansion at [PostgreSQL SELECT](https://www.postgresql.org/docs/current/sql-select.html).)

I have tried mightily, with SQLite version 3.35.0 2021-02-03 13:52:17, to get the RETURNING feature to produce something usable as a subquery that can be used in further (or "outer") operations rather then just producing a result set.

For example, this fails:<code>
 sqlite> with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c from stuffer;
 Error: near "INSERT": syntax error

These are fine:<code>
  insert into t0log (whenCreated,what) select b,c from t0;
  with stuffed as (select b,c from t0) select b,c from stuffed;
</code>, but this is not:<code>
  sqlite> insert into t0log (whenCreated,what) with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c;
  Error: near "INSERT": syntax error

At this point, I conclude that either: (1) I am doing this wrongly; (2) my notion is ill-founded that adding a RETURNING clause to some DML makes that DML into something with further use like a subquery or table-valued function; or (3) SQLite is not yet able to do more with RETURNING clause results than make them available to column value retrieval via the API.

Any thoughts as to which of those 3 cases applies is welcome. Especially welcome would be an incantation that makes DML with a RETURNING clause into something able to participate in containing DML statements. (I must admit that it was this latter utility that had me excited about the new RETURNING feature.)

(Edited to append:)

I should add that the draft [RETURNING doc](https://www.sqlite.org/draft/lang_returning.html) says, "The RETURNING clause is not a statement itself, but a clause that can optionally appear near the end of top-level DELETE, INSERT, and UPDATE statements." This might be interpreted as "The RETURNING clause does not convert the preceding DML into a subquery. It can only be used with a top-level DELETE, INSERT, or UPDATE statement." Hence, I am not claiming that SQLite v3.35.0 does not work as documented. I only claim that the feature might be made more useful.  At the very least, it would be nice if there was a way to get RETURNING results into a temp table without writing library client code to do so.