7.8. WITH 查询 (通用表表达式)

7.8.1. WITH中的SELECT

WITH中SELECT的本意是为了将复杂的查询分解为更简单的部分。一个例子是：

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

它显示了每个产品仅在销售区域的销售总额。WITH子句定义了两个名为 regional_sales 和 top_regions的辅助语句， regional_sales的输出用于top_regions， 而top_regions的输出用于初级的SELECT查询。 这个例子也可以不用WITH来写，但是需要两级嵌套的子SELECT查询。 用这种方法更容易理解。

可选的RECURSIVE修饰符将WITH 从一个单纯的语法方便改变为在SQL标准中不可能实现的功能。 使用RECURSIVE，一个WITH查询可以引用它自己的输出。 一个非常简单的例子是查询1到100的和：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

一个递归WITH查询的一般形式总是一个non-recursive term， 然后UNION（或者UNION ALL），然后一个recursive term， 其中只有递归的术语可以包含一个对查询自己输出的引用。这样一个查询像下面那样执行：

在上面的例子中，在每一步中仅有一个工作表行，并且在后续的步骤中它的值将从 1 升至 100。 在第 100 步，因为WHERE子句的原因没有任何输出，因此查询终止。

递归查询通常用于处理分层或树状结构数据。一个有用的示例查询是查找所有直接或间接的产品的附带部分， 仅提供一个表来显示即时的包含：

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

当使用递归查询的时候，确保查询的递归部分最终不会返回元组是很重要的， 否则查询将会无限的循环下去。有时，通过使用UNION替代UNION ALL 去除掉与前面输出重复的行可以实现这个。然而，通常一个周期不涉及那些完全复制的输出行： 检查一个或几个字段来查看是否存在事先达成的相同点可能是必要的。 处理这种情况的标准方式是计算一个已经访问过的数值的数组。 例如，请考虑下面的查询，使用link字段搜索一个表graph：

WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
)
SELECT * FROM search_graph;

如果link关系包含循环那么这个查询将会循环。 因为我们需要一个"深度"输出，仅改变UNION ALL为UNION 将不会消除循环。相反，我们需要认识到当我们按照特定的链接路径时是否再次得到了相同的行。 我们添加两列path和cycle到倾向循环的查询：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

除了防止循环，该数组值通常是有用的，在它的右边作为代表用来得到任何特定行的"路径"。

在一般情况下，需要检测多个字段来识别一个循环时使用一个行数组。例如， 如果我们需要对比字段f1和f2：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

当您不能确定它们是否会循环的时候，在一个父查询中放置LIMIT是一个对于测试查询有用的技巧。 例如，这个查询将在没有LIMIT的情况下无限循环：

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

它能工作是因为PostgreSQL 的实现评估只有WITH查询的行实际上是通过父查询获取的。 在实际的生产环境下不推荐使用该技巧，因为其它的系统可能以不同的方式工作。 同样，如果您使用外部查询将递归查询结果排序或将它们加入到别的表中， 那么它通常是不工作的，因为在这种情况下外部查询将获取所有WITH 查询的输出。

一个有用的WITH查询属性是每个父查询执行一次它们只做一次评估， 即使它们不止一次地通过父查询或WITH查询引用。所以， 昂贵的需要在多个地方放置的计算可以通过设置WITH查询来避免冗余工作。 另一个可能的应用是防止不必要的副作用函数的多个评估。然而，另一方面，比起普通的子查询， 优化器不能够避开父查询拆分为一个WITH查询的限制。通常WITH 查询将如上评估，没有行限制的父查询可能丢失。（但是，正如上面所说， 如果查询参考只需要数量有限的行，评估可能会很早终止。）

上面的例子只显示了WITH在SELECT中的使用， 但是它也可以用同样的方式附加到INSERT, UPDATE或 DELETE。 在每种情况下它都有效的提供可以在主要的命令中引用的临时表。

7.8.2. WITH中的数据修改语句

你可以在WITH中使用数据修改语句(INSERT,UPDATE 或 DELETE)。这允许你在相同的查询中执行几个不同的操作，一个例子是：

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

这个查询有效的移动products中的行到products_log。 WITH中的DELETE从products中删除指定的行， 并且通过RETURNING子句返回它们的内容； 然后初级查询读取那个输出并且插入到products_log中。

上面例子的一个优点是WITH子句是附加到INSERT， 而不是INSERT中的子SELECT查询。 这是必须的，因为数据修改语句只允许在附加到顶级语句的WITH子句中使用。 然而，因为正常的WITH可见性规则的应用，所以从子SELECT查询中引用WITH 语句的输出是可能的。

在WITH中的数据修改语句通常都有RETURNING子句，就像上面的例子一样。 它是RETURNING子句的输出，不是数据修改语句的目标表， 形成的临时表可以被其他的查询引用。如果WITH中的数据修改语句缺少了 RETURNING子句，那么将没有临时表生成，也就不能被其他的查询引用。 这样的语句将仍然被执行。一个不是特别有用的例子是：

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

这个例子将删除表foo和bar中的所有行。 报告给客户端的受影响行的数量将只包含从bar中删除的行。

数据修改语句中不允许递归的自引用。在某些情况下通过引用递归的WITH 输出，可能绕开这个限制，例如：

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

这个查询将删除一个产品所有直接或非直接的subparts。

WITH中的数据修改语句被直接执行一次，并且总是完成， 独立的主查询读取所有（或者实际上是任意）它们的输出。 注意，这和在WITH中SELECT的规则不同： 就像前一节规定的那样，SELECT的执行直到首级查询需要它的输出时才实施。

WITH中的子语句之间和与主查询之间兼容的执行。因此， 当在WITH中使用数据修改语句时，其他的指定的更新实际上是不可预知发生的。 所有的语句都在相同的快照中执行（见第 13 章）， 所以他们不能"看见"彼此对目标表的影响。这样减轻了实际行更新的不可预知的影响， 并且意味着RETURNING数据是唯一在不同的WITH子语句和主查询间交流变化的方式。 一个例子是：

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

外层的SELECT将在UPDATE动作之前返回原价，而在：

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

中，外层SELECT将返回更新了的数据。

不支持尝试在一个语句中更新相同的行两次。如果尝试了，那么只有一个修改会发生， 但是不容易（或者有时不可能）准确预测是哪一个。这个同样适用于删除一个已经在相同语句中更新了的行： 只有更新被执行。因此你通常应该避免尝试在一个语句中修改一个行两次。特别的， 避免写可能影响被主语句或同级子语句改变了的行的WITH子语句。 这样一个语句的影响将是不可预测的。

目前，任何作为在WITH中的数据修改语句目标的表，不必有扩展到多个语句的条件规则、 ALSO规则和INSTEAD规则。