主页 / dba / 问题 / 190379
Accepted
geeko
Asked: 2017-11-08 20:35:47 +0800 CST

PostgreSQL 10 中的嵌套可写 CTE？

  • 772

我可以嵌套可写 CTE 吗？

WITH "parent_cte" AS (
  WITH "child_cte" AS (
     INSERT INTO ....
  )
  ...
)
...

我知道我可以在另一个 CTE 的 FROM 子句中引用一个 CTE。

postgresql cte

1 个回答

  1. Best Answer

    不，修改 CTE 仍然必须在顶层。

    在Postgres 10.0上测试会产生错误：

    WITH包含数据修改语句的子句必须位于顶层。

    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=#
    • 2

相关问题