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

Can you do this in PostgreSQL?

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

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: 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) .

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

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.

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?