Postgresql 中文操作指南

7.8. WITH Queries (Common Table Expressions) #

WITH 提供了一种方法，用于编写辅助语句以在较大的查询中使用。这些语句通常称为公用表表达式（CTEs），可以认为是定义仅针对一个查询存在的临时表。WITH 子句中的每条辅助语句都可以是 SELECT、INSERT、UPDATE 或 DELETE；WITH 子句本身附加到可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE 的主语句中。

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.

7.8.1. SELECT in WITH #

SELECT 在 WITH 中的基本价值在于将复杂的查询分解为更简单的部分。一个示例是：

The basic value of SELECT in WITH is to break down complicated queries into simpler parts. An example is:

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 就写出来，但我们需要两级嵌套子选择。这样更容易理解。

which displays per-product sales totals in only the top sales regions. The WITH clause defines two auxiliary statements named regional_sales and top_regions, where the output of regional_sales is used in top_regions and the output of top_regions is used in the primary SELECT query. This example could have been written without WITH, but we’d have needed two levels of nested sub-_SELECT_s. It’s a bit easier to follow this way.

7.8.2. Recursive Queries #

可选的 RECURSIVE 修饰符将 WITH 从纯语法便利性变为一个可实现标准 SQL 中不可能实现的功能。使用 RECURSIVE ， WITH 查询可以引用其自己的输出。一个非常简单的示例是此查询，用于对 1 到 100 之间的整数组进行求和：

The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using RECURSIVE, a WITH query can refer to its own output. A very simple example is this query to sum the integers from 1 through 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，其中只有递归项可以包含对查询自己的输出的引用。此类查询的执行方式如下：

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query’s own output. Such a query is executed as follows:

Recursive Query Evaluation

Note

虽然 RECURSIVE 允许递归指定查询，但是在内部，此类查询是迭代评估的。

While RECURSIVE allows queries to be specified recursively, internally such queries are evaluated iteratively.

在上面的示例中，工作表在每个步骤中只有一行，它在连续的步骤中采用 1 到 100 的值。在第 100 步中，由于 WHERE 子句，没有输出，因此查询终止。

In the example above, the working table has just a single row in each step, and it takes on the values from 1 through 100 in successive steps. In the 100th step, there is no output because of the WHERE clause, and so the query terminates.

递归查询通常用于处理层次结构或树形结构数据。一个有用的示例是此查询，它仅在显示直接包含关系的表中找到产品的直接和间接子部件：

Recursive queries are typically used to deal with hierarchical or tree-structured data. A useful example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions:

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

7.8.2.1. Search Order #

在使用递归查询计算树遍历时，你可能希望按深度优先或广度优先按顺序对结果进行排序。可通过在其他数据列旁边计算一个排序列并使用该列在最后对结果进行排序来执行此操作。注意，这并不能实际控制查询评估以何种顺序访问行；在 SQL 中，这是始终因实现而异的。此方法只是提供了一种方便的方式来随后对结果进行排序。

When computing a tree traversal using a recursive query, you might want to order the results in either depth-first or breadth-first order. This can be done by computing an ordering column alongside the other data columns and using that to sort the results at the end. Note that this does not actually control in which order the query evaluation visits the rows; that is as always in SQL implementation-dependent. This approach merely provides a convenient way to order the results afterwards.

要创建深度优先顺序，我们为每行结果计算一个数组，其中包含到目前为止我们已访问的行。例如，请考虑使用 link 字段搜索 tree 表的以下查询：

To create a depth-first order, we compute for each result row an array of rows that we have visited so far. For example, consider the following query that searches a table tree using a link field:

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;

要添加深度优先排序信息，可以这样写：

To add depth-first ordering information, you can write this:

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：

In the general case where more than one field needs to be used to identify a row, use an array of rows. For example, if we needed to track fields f1 and 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;

Tip

在只需要跟踪一个字段的常见情况下省略 ROW() 语法。这允许使用简单的数组而不是复合类型数组，从而提高效率。

Omit the ROW() syntax in the common case where only one field needs to be tracked. This allows a simple array rather than a composite-type array to be used, gaining efficiency.

为创建广度优先顺序，可以添加一列来跟踪搜索深度，例如：

To create a breadth-first order, you can add a column that tracks the depth of the search, for example:

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;

为获得稳定排序，将数据列添加为次要排序列。

To get a stable sort, add data columns as secondary sorting columns.

Tip

递归查询评估算法会以广度优先搜索的顺序生成输出。然而，这是一个实现细节，因此依靠它可能不健全。毫无疑问，每个级别中的行顺序是未定义的，因此在任何情况下可能需要一些明确的顺序。

The recursive query evaluation algorithm produces its output in breadth-first search order. However, this is an implementation detail and it is perhaps unsound to rely on it. The order of the rows within each level is certainly undefined, so some explicit ordering might be desired in any case.

有内置语法可计算深度优先或广度优先排序列。例如：

There is built-in syntax to compute a depth- or breadth-first sort column. For example:

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 的输出行中。

This syntax is internally expanded to something similar to the above hand-written forms. The SEARCH clause specifies whether depth- or breadth first search is wanted, the list of columns to track for sorting, and a column name that will contain the result data that can be used for sorting. That column will implicitly be added to the output rows of the CTE.

7.8.2.2. Cycle Detection #

在使用递归查询时，必须确保查询的递归部分最终不会返回元组，否则查询将无限循环。有时，使用 UNION 代替 UNION ALL 可以通过丢弃重复前一输出行的行来达到此目的。然而，通常一个周期不涉及完全重复的输出行：可能只需要检查一个或几个字段即可了解是否已达到相同的点。处理此类情况的标准方法是计算已访问的值的数组。例如，再次考虑以下使用 link 字段搜索表 graph 的查询：

When working with recursive queries it is important to be sure that the recursive part of the query will eventually return no tuples, or else the query will loop indefinitely. Sometimes, using UNION instead of UNION ALL can accomplish this by discarding rows that duplicate previous output rows. However, often a cycle does not involve output rows that are completely duplicate: it may be necessary to check just one or a few fields to see if the same point has been reached before. The standard method for handling such situations is to compute an array of the already-visited values. For example, consider again the following query that searches a table graph using a link field:

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 关系包含循环，此查询将循环。由于我们需要“深度”输出，仅将 UNION ALL 改为 UNION 不会消除循环。相反，我们需要在遵循特定链接路径时识别是否再次到达同一行。我们将两列 is_cycle 和 path 添加到容易循环的查询中：

This query will loop if the link relationships contain cycles. Because we require a “depth” output, just changing UNION ALL to UNION would not eliminate the looping. Instead we need to recognize whether we have reached the same row again while following a particular path of links. We add two columns is_cycle and path to the loop-prone query:

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;

除了防止循环外，数组值本身通常也很有用，因为它表示到达任何特定行的“路径”。

Aside from preventing cycles, the array value is often useful in its own right as representing the “path” taken to reach any particular row.

在需要检查多个字段才能识别循环的一般情况下，请使用行数组。例如，如果我们需要比较字段 f1 和 f2：

In the general case where more than one field needs to be checked to recognize a cycle, use an array of rows. For example, if we needed to compare fields f1 and 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;

Tip

在只需要检查一个字段即可识别循环的常见情况下，省略 ROW() 语法。这允许使用简单的数组而不是复合类型数组，从而提高效率。

Omit the ROW() syntax in the common case where only one field needs to be checked to recognize a cycle. This allows a simple array rather than a composite-type array to be used, gaining efficiency.

有内置语法可以简化循环检测。上述查询也可以这样编写：

There is built-in syntax to simplify cycle detection. The above query can also be written like this:

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 的输出行中。

and it will be internally rewritten to the above form. The CYCLE clause specifies first the list of columns to track for cycle detection, then a column name that will show whether a cycle has been detected, and finally the name of another column that will track the path. The cycle and path columns will implicitly be added to the output rows of the CTE.

Tip

循环路径列的计算方式与上一节中显示的深度优先排序列相同。一个查询既可以有 SEARCH 从句，也可以有 CYCLE 从句，但是深度优先搜索规范和循环检测规范将创建冗余计算，因此仅使用 CYCLE 从句并按路径列排序会更有效。如果需要广度优先排序，则指定 SEARCH 和 CYCLE 都可能有用。

The cycle path column is computed in the same way as the depth-first ordering column show in the previous section. A query can have both a SEARCH and a CYCLE clause, but a depth-first search specification and a cycle detection specification would create redundant computations, so it’s more efficient to just use the CYCLE clause and order by the path column. If breadth-first ordering is wanted, then specifying both SEARCH and CYCLE can be useful.

在你不确定查询是否可能循环时，用于测试查询的一个有用的技巧是在父查询中放置一个 LIMIT。例如，如果没有 LIMIT，此查询将无限循环：

A helpful trick for testing queries when you are not certain if they might loop is to place a LIMIT in the parent query. For example, this query would loop forever without the LIMIT:

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

这是因为 PostgreSQL 的实现仅评估父查询实际获取的 WITH 查询的行数。不建议在生产中使用此技巧，因为其他系统可能工作方式不同。此外，如果你让外部查询对递归查询结果进行排序或将它们连接到其他某个表，它通常不起作用，因为在这种情况下，外部查询通常会尝试获取 WITH 查询的所有输出。

This works because PostgreSQL’s implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query. Using this trick in production is not recommended, because other systems might work differently. Also, it usually won’t work if you make the outer query sort the recursive query’s results or join them to some other table, because in such cases the outer query will usually try to fetch all of the WITH query’s output anyway.

7.8.3. Common Table Expression Materialization #

WITH 查询的一个有用特性是它们通常只在执行父查询时评估一次，即使它们被父查询或同级 WITH 查询引用多次。因此，可以在 WITH 查询中放置多个位置所需的昂贵计算，以避免冗余工作。另一个可能的应用是防止对具有副作用的函数进行多次不必要的评估。然而，硬币的另一面是，优化器无法将父查询中的限制推送到多次引用的 WITH 查询中，因为这可能会影响 WITH 查询输出的所有用途，而它只应影响一个。多次引用的 WITH 查询将按原样评估，不会抑制父查询可能随后丢弃的行。（但是，如上所述，如果对查询的引用仅需要有限数量的行，则评估可能会提前停止。）

A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is not able to push restrictions from the parent query down into a multiply-referenced WITH query, since that might affect all uses of the WITH query’s output when it should affect only one. The multiply-referenced WITH query will be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)

然而，如果一个 WITH 查询是非递归的并且没有副作用（即它是 SELECT，不包含不稳定的函数），那么它可以折入父查询，从而允许对两个查询级别进行联合优化。默认情况下，如果父查询仅引用 WITH 查询一次，而非多次引用 WITH 查询，则会发生这种情况。可以通过指定 MATERIALIZED 强制单独计算 WITH 查询，或指定 NOT MATERIALIZED 强制将其合并到父查询中来覆盖该决策。后一种选择有重复计算 WITH 查询的风险，但如果 WITH 查询的每次使用仅需要 WITH 查询的全部输出的一小部分，它仍然可以节省开销。

However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query’s full output.

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

A simple example of these rules is

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

该 WITH 查询将被折叠，产生与以下查询相同的执行计划：

This WITH query will be folded, producing the same execution plan as

SELECT * FROM big_table WHERE key = 123;

尤其地，如果 key 上包含一个索引，那么该索引可能会被用来获取恰好具有 key = 123 的那些行。另一方面，对于以下查询，

In particular, if there’s an index on key, it will probably be used to fetch just the rows having key = 123. On the other hand, in

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 的临时副本，该副本随后将和自身联接，而不会使用任何索引。如果按照以下方式编写，该查询的执行效率将大大提高，

the WITH query will be materialized, producing a temporary copy of big_table that is then joined with itself — without benefit of any index. This query will be executed much more efficiently if written as

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 的扫描。

so that the parent query’s restrictions can be applied directly to scans of big_table.

一个 NOT MATERIALIZED 可能会产生不良影响的示例是

An example where NOT MATERIALIZED could be undesirable is

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 每个表行仅计算一次，而不是两次。

Here, materialization of the WITH query ensures that very_expensive_function is evaluated only once per table row, not twice.

上述示例中仅展示了 WITH 与 SELECT 搭配使用，但也可以将其以同样的方式附加到 INSERT、UPDATE、DELETE 或 MERGE。在每一种情况下，它都有效地提供了一个或多个临时表，可以在主命令中引用它们。

The examples above only show WITH being used with SELECT, but it can be attached in the same way to INSERT, UPDATE, DELETE, or MERGE. In each case it effectively provides temporary table(s) that can be referred to in the main command.

7.8.4. Data-Modifying Statements in WITH #

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

You can use most data-modifying statements (INSERT, UPDATE, or DELETE, but not MERGE) in WITH. This allows you to perform several different operations in the same query. An example is:

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。

This query effectively moves rows from products to products_log. The DELETE in WITH deletes the specified rows from products, returning their contents by means of its RETURNING clause; and then the primary query reads that output and inserts it into products_log.

上述示例的一个关键之处是，WITH 条款附加到了 INSERT，而不是 INSERT 内的子 SELECT。这是必需的，因为仅允许在附加到顶级语句的 WITH 条款中使用数据修改语句。然而，仍然会应用常规 WITH 可见性规则，因此可以从子 SELECT 引用 WITH 语句的输出。

A fine point of the above example is that the WITH clause is attached to the INSERT, not the sub-SELECT within the INSERT. This is necessary because data-modifying statements are only allowed in WITH clauses that are attached to the top-level statement. However, normal WITH visibility rules apply, so it is possible to refer to the WITH statement’s output from the sub-SELECT.

在_WITH_中，数据修改语句通常有 RETURNING_子句（参见 Section 6.4），如上例所示。是对 _RETURNING 子句的输出，not_数据修改语句的目标表，该表形成了其余查询可引用的临时表。如果 _WITH 中的数据修改语句缺少 RETURNING 子句，那么它不会形成任何临时表，并且在其余查询中无法引用它。此类语句仍将执行。一个不太有用的示例是：

Data-modifying statements in WITH usually have RETURNING clauses (see Section 6.4), as shown in the example above. It is the output of the RETURNING clause, not the target table of the data-modifying statement, that forms the temporary table that can be referred to by the rest of the query. If a data-modifying statement in WITH lacks a RETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless. A not-particularly-useful example is:

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

该示例将从表 foo 和 bar 中删除所有行。报告给客户端的影响行数仅包括从 bar 中删除的行。

This example would remove all rows from tables foo and bar. The number of affected rows reported to the client would only include rows removed from bar.

数据修改语句中不允许递归自引用。在某些情况下，可以通过引用递归 WITH 的输出，来解决此限制，例如：

Recursive self-references in data-modifying statements are not allowed. In some cases it is possible to work around this limitation by referring to the output of a recursive WITH, for example:

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);

该查询将删除产品的全部直接和间接子部件。

This query would remove all direct and indirect subparts of a product.

WITH 中的数据修改语句仅执行一次，且始终执行到完成，与主查询是否读取了它们全部或部分输出无关。请注意，这不等同于 WITH 中 SELECT 的规则：如前一节中所述，仅根据主查询的需求来执行 SELECT。

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

_WITH_中的子语句彼此并发执行，并与主查询并发执行。因此，当在 _WITH_中使用数据修改语句时，指定的更新实际发生的顺序是不可预测的。所有语句都使用相同的 _snapshot_执行（参见 Chapter 13），因此它们无法“看到”彼此对目标表的影响。这减轻了行更新实际顺序不可预测的影响，并意味着_RETURNING_数据是不同_WITH_子语句和主查询之间进行通信的唯一方式。其中一个示例是，在

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another’s effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. An example of this is that in

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

在示例中，外部 SELECT 将在执行了 UPDATE 操作之前返回原始价格，而在示例中，

the outer SELECT would return the original prices before the action of the UPDATE, while in

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

外部 SELECT 将返回更新后的数据。

the outer SELECT would return the updated data.

不支持尝试在单一语句中更新同一行两次。仅发生其中一个修改，但难以可靠地预测哪一个修改会发生（有时候甚至不可能）。这同样适用于删除在同一语句中已更新的行：仅执行更新。因此，你通常应该避免尝试在单一语句中修改同一行两次。尤其避免编写可能影响由主语句或兄弟子语句更改的相同行的 WITH 子语句。这样的语句的影响是不可预测的。

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

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

At present, any table used as the target of a data-modifying statement in WITH must not have a conditional rule, nor an ALSO rule, nor an INSTEAD rule that expands to multiple statements.