将SELECT用于WITH的基本价值是把复杂查询分解成更简单的部分。例如：

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查询的一般形式始终是一个非递归项，接着是UNION（或UNION ALL），然后是一个递归项，其中只有递归项才能包含对查询自身输出的引用。这样的查询按以下方式执行：

在上面的示例中，工作表在每一步都只有一行，并且在连续步骤中依次取值 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 * pr.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，通过丢弃与先前输出行重复的行来做到这一点。不过，循环往往并不涉及完全重复的输出行：可能需要只检查一个或几个字段，以判断是否曾到达同一点。处理这种情况的标准方法是计算一个包含已访问值的数组。例如，考虑以下查询，它搜索表graph，使用其中的link字段：

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关系包含循环，此查询就会循环。由于需要输出“depth”，仅把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查询会按书写的样子求值，不会抑制父查询稍后可能丢弃的行。（不过，如上所述，如果对该查询的引用只需要有限数量的行，求值可能会提前停止。）

但是，如果WITH查询是非递归且无副作用的（即它是不含可变函数的SELECT），就可以折叠到父查询中，从而允许两个查询层级联合优化。默认情况下，如果父查询只引用该WITH查询一次，就会进行这种折叠；如果多次引用该WITH查询，则不会。可以指定MATERIALIZED来强制单独计算该WITH查询，或者指定NOT MATERIALIZED来强制将它合并到父查询中，以覆盖该决定。后一种选择可能导致重复计算WITH查询；但如果每次使用该WITH查询都只需要该WITH查询完整输出中的一小部分，仍可能节省总体工作量。

这些规则的一个简单示例是

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

这个WITH查询会被折叠，从而生成与下面语句相同的执行计划：

SELECT * FROM big_table WHERE key = 123;

特别是，如果key上存在索引，它很可能会被用来只提取那些key = 123的行。另一方面，在

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

WITH查询将被物化，生成big_table的一个临时副本，然后再与自身连接 — 因而无法从任何索引中获益。 如果把它写成下面这样，这个查询会高效得多：

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

这样父查询中的限制条件就可以直接应用到对big_table的扫描上。

一个可能不适合使用NOT MATERIALIZED的示例如下：

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

在这里，WITH查询的物化确保very_expensive_function每个表行只计算一次，而不是两次。

上面的示例仅显示了WITH与SELECT一起使用， 但它可以以相同的方式附加到INSERT、UPDATE或DELETE。 在每种情况下，它都等效于提供了可在主命令中引用的临时表。

你可以在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子句（见Section 6.4）。构成其他查询可引用临时表的，是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);

此查询会删除某产品的所有直接和间接子部件。

WITH中的数据修改语句只会执行一次，并且总会执行到完成，而不管主查询是否读取了它们的全部输出，甚至是否读取了任何输出。注意这与WITH中SELECT的规则不同：正如前一小节所述，SELECT只会执行到主查询实际需要其输出为止。

WITH中的子语句彼此之间以及与主查询是并发执行的。因此，在WITH中使用数据修改语句时，实际更新发生的顺序是不可预知的。所有语句都使用同一个快照执行（参见Chapter 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规则。