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 查询的一般形式总是先写一个非递归项，再写 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

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree;
  

  WITH RECURSIVE search_tree(id, link, data, path) AS (
      SELECT t.id, t.link, t.data, ARRAY[t.id]
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, path || t.id
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY path;
  

  WITH RECURSIVE search_tree(id, link, data, path) AS (
      SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY path;
  

  WITH RECURSIVE search_tree(id, link, data, depth) AS (
      SELECT t.id, t.link, t.data, 0
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data, depth + 1
      FROM tree t, search_tree st
      WHERE t.id = st.link
  )
  SELECT * FROM search_tree ORDER BY depth;
  

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  ) SEARCH DEPTH FIRST BY id SET ordercol
  SELECT * FROM search_tree ORDER BY ordercol;

  WITH RECURSIVE search_tree(id, link, data) AS (
      SELECT t.id, t.link, t.data
      FROM tree t
    UNION ALL
      SELECT t.id, t.link, t.data
      FROM tree t, search_tree st
      WHERE t.id = st.link
  ) SEARCH BREADTH FIRST BY id SET ordercol
  SELECT * FROM search_tree ORDER BY ordercol;
  

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    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;

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

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

  WITH RECURSIVE search_graph(id, link, data, depth) AS (
      SELECT g.id, g.link, g.data, 0
      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
  ) CYCLE id SET is_cycle USING path
  SELECT * FROM search_graph;
  

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

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 或 MERGE。在每种情况下，它都等效于提供了可在主命令中引用的临时表。

你可以在 WITH 中使用大多数数据修改语句（INSERT、UPDATE 或 DELETE，但不能使用 MERGE）。这样就可以在同一查询中执行多个不同的操作。例如：

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 子句（见第 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 中使用数据修改语句时，实际更新发生的顺序是不可预知的。所有语句都使用同一个快照执行（参见第 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 规则。