Can I nest writeable CTEs?
WITH "parent_cte" AS (
WITH "child_cte" AS (
INSERT INTO ....
)
...
)
...
I know that I can refer to a CTE in the FROM clause of another CTE.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityCan I nest writeable CTEs?
WITH "parent_cte" AS (
WITH "child_cte" AS (
INSERT INTO ....
)
...
)
...
I know that I can refer to a CTE in the FROM clause of another CTE.
No, modifying CTEs still have to be at the top level.
Testing at Postgres 10.0 produces the error:
WITH
clause containing a data-modifying statement must be at the top level.
select version() ;
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)
x=# create table t (id int, x int) ;
CREATE TABLE
x=# insert into t (id, x) select i, i*2 from generate_series(1,10) as g(i) ;
INSERT 0 10
x=# select * from t ;
id | x
----+----
1 | 2
2 | 4
3 | 6
4 | 8
5 | 10
6 | 12
7 | 14
8 | 16
9 | 18
10 | 20
(10 rows)
x=# with a as
( with b as
( update t set x = x + 3 returning *
)
select * from b
)
select * from a ;
ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 2: with b as (update t set x = x + 3 returning *) select * fr...
^
x=#