可写CTE

概述

可写 CTE 在 CTE（公共表表达式）中添加了 "(INSERT | UPDATE | DELETE) .. [RETURNING ..]"，无论是在 WITH 子句还是顶层语句中。

语法

   [WITH with_query [, ...]
       plannable_statement

其中 with_query 是

   with_query_name [ ( column_name [, ...] ) ] AS ( plannable_statement )

以及可计划语句（参见 gram.y 以了解该术语的来源）查询是

   ( select | insert | update | delete )

术语

DML

INSERT、DELETE 或 UPDATE 语句。

派生表

由表表达式返回的临时表（元组集）。

通用规则

• 顶层 DML WITH 子句使用单个快照执行。此查询将从“x”中删除所有行，并在 DELETE 之前返回“x”中的行数（即删除的行数）。

   WITH t1 AS (DELETE FROM x)
      SELECT count(*) FROM x;

• DML 作为单独的计划执行。
• WITH 列表中没有 RECURSIVE 子句的 DML 只能引用在其中之前指定的其他表表达式。此行为与普通的 SELECT CTE 相同。例如
  

   -- this should work
   WITH t1 AS(DELETE FROM src RETURNING *),
   t2 AS(INSERT INTO dest SELECT * FROM t1 RETURNING *)
   SELECT * FROM t2;
   
   -- this should raise error
   WITH t2 AS(INSERT INTO dest SELECT * FROM t1 RETURNING *),
   t1 AS(DELETE FROM src RETURNING *)
   SELECT * FROM t2;

• AFTER 触发器在整个查询执行完毕之前不会运行。
• 你不能在 wCTE 查询上创建视图。此限制可能在将来被移除。
• 允许在 WITH 中放置没有 RETURING 子句的 DML，但禁止引用该派生表。
  

   -- this is ok
   WITH t1 AS(DELETE FROM src), t2 AS(SELECT * FROM dest)
   SELECT * FROM t2;
   
   -- this raises an error
   WITH t1 AS(DELETE FROM src), t2 AS(SELECT * FROM dest)
   SELECT * FROM t1;

• WITH 子句中的 DML 仅允许在顶层 WITH 中。

   -- raise an error
   SELECT * FROM(WITH t1 AS(DELETE FROM src) SELECT * FROM t1)s;

实现

[D] 允许“主查询”为 DML

• 对于 9.0，这方面存在一些问题，参见 http://archives.postgresql.org/pgsql-hackers/2010-02/msg01065.php

用法

• 将行从 src 移动到 dest，并立即返回它们。

   WITH t1 AS (DELETE FROM src RETURNING *),
      INSERT INTO dest SELECT * FROM t1 RETURNING *;

兼容性

由于 DML 上的 RETURNING 子句是 PostgreSQL 的扩展，因此可写 CTE 不在当前标准中。但是，SQL 2011 可能提到了增量表，这可能类似于可写 CTE。