Use of DML with RETURNING clause as a subquery
(1.2) By Larry Brasfield (LarryBrasfield) on 2021-02-03 16:52:30 edited from 1.1 [link]
Using this table,<code> CREATE TABLE t0( a INTEGER PRIMARY KEY, b DATE DEFAULT CURRENT_TIMESTAMP, c INTEGER ); </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 </code> 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 sqlite> </code>. 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.
(2) By Richard Hipp (drh) on 2021-02-03 17:27:44 in reply to 1.2 [link]
DML statements cannot be used in subqueries. I suppose that should be mentioned in the documentation. Can you do this in PostgreSQL?
(3) By David Raymond (dvdraymond) on 2021-02-03 18:12:36 in reply to 1.2 [link]
Just as a note this query will also give an error in PostgreSQL as well: > insert into t0log (whenCreated,what) with stuffer as (INSERT INTO t0(c) VALUES(random()) RETURNING b,c) select b,c; ` testing=> insert into t0log (when_created, what) with stuffer as (insert into t0 (c) values (random()) returning b, c) select b, c; ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 1: insert into t0log (when_created, what) with stuffer as (inse... ^ Time: 13.069 ms ` And needs to be re-written with the WITH part first. ` testing=> with stuffer as (insert into t0 (c) values (random()) returning b, c) insert into t0log (when_created, what) select b, c from stuffer; INSERT 0 1 Time: 0.934 ms `
(4) By Larry Brasfield (LarryBrasfield) on 2021-02-03 18:27:59 in reply to 2 [link]
Well, none of the above trials fared any better in PostgreSQL. (It moans, "ERROR: WITH clause containing a data-modifying statement must be at the top level".) However, this screen-scrape shows what does succeed:<code> postgres=# CREATE TABLE t0( postgres(# a INTEGER PRIMARY KEY, postgres(# b DATE DEFAULT CURRENT_TIMESTAMP, postgres(# c INTEGER postgres(# ); CREATE TABLE postgres=# create table t0log (whenCreated DATE, what integer); CREATE TABLE postgres=# with stuffed as (INSERT INTO t0(a,b,c) VALUES(5,CURRENT_DATE,random()) returning b,c) insert into t0log(whenCreated,what) select b,c from stuffed; INSERT 0 1 postgres=# select * from t0log; whencreated | what -------------+------ 2021-02-03 | 1 (1 row) </code>. (The 1 came in the what column comes from PostgreSQL's random() returning a float between 0 and near 1, which is rounded going into t0.c, and integer.) I will suggest a few RETURNING doc changes as I study it further.
(5) By Larry Brasfield (LarryBrasfield) on 2021-02-04 16:20:46 in reply to 2 [link]
Re: > 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.
(6) By Mark Lawrence (mark) on 2021-02-05 08:49:12 in reply to 2
The following in PostgreSQL only inserts a single row: ``` CREATE TABLE t1(a serial PRIMARY KEY); WITH x AS ( INSERT INTO t1 DEFAULT VALUES RETURNING a ) SELECT a FROM x UNION ALL SELECT a FROM x; ``` If I recall correctly, SQLite "embeds" the WITH table as a subquery inside each use within the main statement. Would this result in the insertion of two rows?
(7.1) By Gunter Hick (gunter_hick) on 2021-02-05 09:00:31 edited from 7.0 in reply to 6 [link]
Possibly. The type specification "serial" is interpreted as numeric affinity, so it is distinct from the rowid. It's default value would be NULL, but due to a historical oversight, SQLite should accept a NULL in the primary key. Edit: And UNION ALL does not discard duplicate values. Using UNION may just insert two records but return only one value...