WITH查询（公共表表达式） #WITH提供了一种为更大查询编写辅助语句的方法。这些语句通常被称为公共表表达式或CTE，可以视为仅在单个查询期间存在的临时表定义。WITH子句中的每个辅助语句都可以是SELECT、INSERT、UPDATE、DELETE或MERGE；而WITH子句本身则附加到一个主语句上，该主语句同样可以是SELECT、INSERT、UPDATE、DELETE或MERGE。
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查询的一般形式总是先写一个非递归项，再写UNION（或UNION ALL），然后写一个递归项；其中只有递归项可以包含对查询自身输出的引用。这样的查询执行过程如下：
递归查询求值
计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。
只要工作表不为空，重复下列步骤：
计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNION ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。
用中间表的内容替换工作表的内容，然后清空中间表。
虽然RECURSIVE允许递归指定查询，但在内部这样的查询是迭代评估的。
在上面的示例中，工作表在每一步都只有一行，并且在连续步骤中依次取值 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
当使用递归查询计算树遍历时，你可能希望把结果按深度优先或广度优先顺序排列。这可以通过在其他数据列旁边额外计算一个排序列，并在最后据此对结果排序来实现。请注意，这实际上并不能控制查询求值访问各行的先后顺序；在 SQL 中，这一行为始终依赖于具体实现。这种方法只是为事后排列结果提供了一种方便方式。
为了生成深度优先顺序，可以为每个结果行计算一个到目前为止已访问行的数组。例如，考虑下面这个使用link字段搜索表tree的查询：
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;
在更一般的情形下，如果需要使用多个字段来标识一行，就要使用行数组。例如，如果我们需要跟踪f1和f2字段：
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;
在通常只需要跟踪一个字段的情况下，可以省略ROW()语法。这样就能使用简单数组而不是复合类型数组，从而提高效率。
为了生成广度优先顺序，可以添加一个用于跟踪搜索深度的列，例如：
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;
这种语法在内部会扩展为与上面手写形式类似的内容。SEARCH子句指定是要进行深度优先还是广度优先搜索、要跟踪哪些列用于排序，以及哪个列名将保存可供排序使用的结果数据。该列会被隐式添加到 CTE 的输出行中。
在使用递归查询时，务必要确保查询的递归部分最终不会再返回元组，否则查询就会无限循环。有时把UNION ALL改成UNION，通过丢弃与先前输出行重复的行，就可以达到这个目的。不过，循环往往并不表现为整行完全重复；有时只需要检查一个或几个字段，就能判断是否再次到达了同一个点。处理这种情况的标准方法，是计算一个包含已访问值的数组。例如，考虑下面这个使用link字段搜索表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
)
SELECT * FROM search_graph;
如果link关系中包含环，这个查询就会循环。因为我们需要输出“depth”，仅仅把UNION ALL改成UNION并不能消除循环。相反，当我们沿着某条特定链接路径搜索时，需要识别自己是否再次到达了同一行。可以给这个容易产生循环的查询增加两个列is_cycle和path：
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;
除了用于阻止循环之外，这个数组值本身也常常很有用，因为它表示了到达任意特定行所经历的“路径”。
在更一般的情形下，如果需要检查多个字段才能识别一个环，就要使用行数组。例如，如果需要比较f1和f2字段：
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;
在通常只有一个字段需要检查即可识别环的情况下，可以省略ROW()语法。这样就能使用简单数组而不是复合类型数组，从而提高效率。
有一种内置语法可以简化环检测。上面的查询也可以写成这样：
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
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
它在内部会被重写成上面的形式。CYCLE子句首先指定要跟踪哪些列来检测循环，然后指定一个列名，用于显示是否检测到了循环，最后再指定另一个列名，用于跟踪路径。循环列和路径列都会被隐式添加到 CTE 的输出行中。
循环路径列的计算方式与上一节展示的深度优先排序列相同。一个查询可以同时包含SEARCH和CYCLE子句，但深度优先搜索规范和环检测规范会产生重复计算，因此通常只使用CYCLE子句并按路径列排序会更高效。如果需要广度优先排序，那么同时指定SEARCH和CYCLE就会比较有用。
当你不确定查询是否可能循环时，一个测试查询的有用技巧是在父查询中放一个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查询通常会按书写的样子完整求值，而不会抑制那些父查询稍后可能丢弃的行。（不过，如前所述，如果对该查询的引用只请求有限数量的行，那么求值可能会提前停止。）
但是，如果WITH查询是非递归且无副作用的（也就是说，它是一个不包含可变函数的SELECT），那么它就可以被折叠进父查询，从而允许两个查询层级进行联合优化。 默认情况下，如果父查询只引用该WITH查询一次，就会发生这种折叠；如果引用多于一次，则不会发生。 你可以通过指定MATERIALIZED来强制该WITH查询单独计算，也可以通过指定NOT MATERIALIZED来强制它并入父查询。 后一种选择有重复计算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中的数据修改语句 #你可以在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子句（见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规则。
如果您发现文档中有不正确的内容、与您使用特定功能的经验不符或需要进一步说明，请使用此表单来报告文档问题。