バージョンごとのドキュメント一覧

7.8. WITH問い合わせ(共通テーブル式) #

<title><literal>WITH</literal> Queries (Common Table Expressions)</title>

<literal>WITH</literal> 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 <acronym>CTE</acronym>s, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a <literal>WITH</literal> clause can be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>; and the <literal>WITH</literal> clause itself is attached to a primary statement that can also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>. 《マッチ度[91.376912]》WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、1つの問い合わせのために存在する一時テーブルを定義すると考えることができます。 WITH句内の補助文はそれぞれSELECTINSERTUPDATEまたはDELETEを取ることができます。 そしてWITH句自身は、これもSELECTINSERTUPDATEDELETE、またはMERGEを取ることができる主文に付与されます。

7.8.1. WITH内のSELECT #

<title><command>SELECT</command> in <literal>WITH</literal></title>

The basic value of <command>SELECT</command> in <literal>WITH</literal> is to break down complicated queries into simpler parts. An example is: 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;

which displays per-product sales totals in only the top sales regions. The <literal>WITH</literal> clause defines two auxiliary statements named <structname>regional_sales</structname> and <structname>top_regions</structname>, where the output of <structname>regional_sales</structname> is used in <structname>top_regions</structname> and the output of <structname>top_regions</structname> is used in the primary <command>SELECT</command> query. This example could have been written without <literal>WITH</literal>, but we'd have needed two levels of nested sub-<command>SELECT</command>s. It's a bit easier to follow this way. これは販売トップの地域(region)のみから製品ごとの売上高を表示します。 WITH句は、regional_salestop_regionsという名前の2つの補助文を定義します。 ここで、regional_salesの出力はtop_regions内で使用され、top_regionsSELECT主問い合わせで使用されます。 この例は WITHなしでも記述できますが、二階層の入れ子のsub-SELECTを必要とします。この方法に従うほうが多少扱いやすいです。

7.8.2. 再帰的問い合わせ #

<title>Recursive Queries</title>

The optional <literal>RECURSIVE</literal> modifier changes <literal>WITH</literal> from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using <literal>RECURSIVE</literal>, a <literal>WITH</literal> query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100: オプションの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;

The general form of a recursive <literal>WITH</literal> query is always a <firstterm>non-recursive term</firstterm>, then <literal>UNION</literal> (or <literal>UNION ALL</literal>), then a <firstterm>recursive term</firstterm>, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows: 再帰的WITH問い合わせの汎用形式は常に、非再帰的表現(non-recursive term)、そしてUNION(またはUNION ALL)、そして再帰的表現(recursive term)です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。 このような問い合わせは以下のように実行されます。

再帰的問い合わせの評価

<title>Recursive Query Evaluation</title>
  1. Evaluate the non-recursive term. For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard duplicate rows. Include all remaining rows in the result of the recursive query, and also place them in a temporary <firstterm>working table</firstterm>. 非再帰的表現を評価します。 UNION(ただしUNION ALLは除きます)では、重複行を廃棄します。 その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時作業テーブルに置きます。

  2. So long as the working table is not empty, repeat these steps: 作業テーブルが空でないのであれば以下の手順を繰り返します。

    1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For <literal>UNION</literal> (but not <literal>UNION ALL</literal>), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary <firstterm>intermediate table</firstterm>. 再帰自己参照を作業テーブルの実行中の内容で置換し、再帰的表現を評価します。 UNION(ただしUNION ALLは除きます)に対し、重複行と前の結果行と重複する行を破棄します。 その再帰的問い合わせの結果の残っているすべての行を盛り込み、同時にそれらを一時中間テーブルに置きます。

    2. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table. 中間テーブルの内容で作業テーブルの内容を差し替え、中間テーブルを空にします。

注記

While <literal>RECURSIVE</literal> allows queries to be specified recursively, internally such queries are evaluated iteratively. RECURSIVEでは問い合わせを再帰的(recursively)に指定できますが、内部的にはそのような問い合わせは反復的(iteratively)に評価されます。

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 <literal>WHERE</literal> clause, and so the query terminates. 上記の例で、作業テーブルはそれぞれの手順での単なる単一行で、引き続く作業で1から100までの値を獲得します。 100番目の作業で、WHERE句による出力が無くなり、問い合わせが終了します。

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: 再帰的問い合わせは階層的、またはツリー構造データに対処するため一般的に使用されます。 実用的な例は、直接使用する部品を表すテーブル1つのみが与えられ、そこから製品すべての直接・間接部品を見つける次の問い合わせです。

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.2. サイクル検出 #

<title>Cycle Detection</title>

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 <literal>UNION</literal> instead of <literal>UNION ALL</literal> 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 <structname>graph</structname> using a <structfield>link</structfield> field: 再帰的問い合わせを扱う場合、問い合わせの再帰部分が最終的にはタプルを返さないようにすることが重要です。 そうしなければ、問い合わせが永久にループしてしまうからです。 UNION ALLの替わりにUNIONを使用することで、重複する前回の出力行が廃棄され、これを実現できることもあるでしょう。 しかし、各周期が完全に重複している行を含まないこともよくあり、そのような場合は、1つまたは少数のフィールドを検査して、同じ場所に既に到達したかどうかを調べる必要があるかもしれません。 このような状態を取り扱う標準手法は、既に巡回された値の配列を計算することです。 例えば、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;

This query will loop if the <structfield>link</structfield> relationships contain cycles. Because we require a <quote>depth</quote> output, just changing <literal>UNION ALL</literal> to <literal>UNION</literal> 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 <structfield>is_cycle</structfield> and <structfield>path</structfield> to the loop-prone query: この問い合わせはlink関係が循環を含んでいればループします。 depth出力を要求しているので、UNION ALLUNIONに変えるだけでは、ループを取り除くことができません。 その代わり、linkの特定の経路をたどっている間に、同じ行に到達したかどうかを認識する必要があります。 このループしやすい問い合わせに、pathcycleの2列を加えます。

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 <quote>path</quote> taken to reach any particular row. 巡回防止の他に、特定行に到達する際に選ばれたpath それ自体を表示するため、配列値はしばしば利用価値があります。

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 <structfield>f1</structfield> and <structfield>f2</structfield>: 循環を認識するために検査するために必要なフィールドが複数存在する一般的な状況では、行の配列を使用します。 例えば、フィールドf1f2を比較する必要があるときは次のようにします。

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;

ヒント

Omit the <literal>ROW()</literal> 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. 循環を認識するために検査するために必要なフィールドが1つだけである一般的な場合では、ROW()構文を削除します。 これで、複合型配列ではなく単純配列で済むので、効率も上がります。

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;

and it will be internally rewritten to the above form. The <literal>CYCLE</literal> 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. また、内部的に上記の形式に書き換えられます。 CYCLE句は、最初にサイクル検出のために追跡する列のリストを指定し、次にサイクルが検出されたかどうかを示す列名、最後にパスを追跡する別の列の名前を指定します。 サイクル列とパス列は、CTEの出力行に暗黙的に追加されます。

ヒント

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 <literal>SEARCH</literal> and a <literal>CYCLE</literal> 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 <literal>CYCLE</literal> clause and order by the path column. If breadth-first ordering is wanted, then specifying both <literal>SEARCH</literal> and <literal>CYCLE</literal> can be useful. サイクル・パス列は、前のセクションで示した深さ優先順序列と同じ方法で計算されます。 問い合わせにはSEARCH句とCYCLE句の両方を含めることができますが、深さ優先検索指定とサイクル検出指定では冗長な計算が作成されるため、CYCLE句を使用してパス列で順序付けるだけの方が効率的です。 幅優先順序が必要な場合は、SEARCHCYCLEの両方を指定すると便利です。

A helpful trick for testing queries when you are not certain if they might loop is to place a <literal>LIMIT</literal> in the parent query. For example, this query would loop forever without the <literal>LIMIT</literal>: ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせにLIMITを配置します。 例えば、以下の問い合わせはLIMITがないと永久にループします。

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

This works because <productname>PostgreSQL</productname>'s implementation evaluates only as many rows of a <literal>WITH</literal> 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 <literal>WITH</literal> query's output anyway. これが動作するのは、PostgreSQLの実装が、実際に親問い合わせで取り出されるのと同じ数のWITH問い合わせの行のみを評価するからです。 この秘訣を実稼働環境で使用することは勧められません。 他のシステムでは異なった動作をする可能性があるからです。 同時に、もし外部問い合わせを再帰的問い合わせの結果を並べ替えたり、またはそれらを他のテーブルと結合するような書き方をした場合、動作しません。 このような場合、外部問い合わせは通常、WITH問い合わせの出力をとにかくすべて取り込もうとするからです。

7.8.3. 共通テーブル式内マテリアライゼーション #

<title>Common Table Expression Materialization</title>

A useful property of <literal>WITH</literal> 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 <literal>WITH</literal> queries. Thus, expensive calculations that are needed in multiple places can be placed within a <literal>WITH</literal> 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 <literal>WITH</literal> query, since that might affect all uses of the <literal>WITH</literal> query's output when it should affect only one. The multiply-referenced <literal>WITH</literal> 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問い合わせの特性は、親問い合わせ、もしくは兄弟WITH問い合わせによりたとえ1回以上参照されるとしても、通常は親問い合わせ実行で1回だけ評価されることです。 したがって、複数の場所で必要な高価な計算は、冗長作業を防止するためWITH問い合わせの中に配置することができます。 他にありうる適用としては、望まれない副作用のある関数の多重評価を避けることです。 しかし、反対の見方をすれば、オプティマイザが親クエリから複数参照されるWITH問い合わせに制約を押し下げることができないということになります。 これは、WITH問い合わせの出力が1つのみに影響する場合、その出力のすべての使用に影響する可能性があるためです。 複数参照されるWITH問い合わせは、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。 (しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。)

However, if a <literal>WITH</literal> query is non-recursive and side-effect-free (that is, it is a <literal>SELECT</literal> 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 <literal>WITH</literal> query just once, but not if it references the <literal>WITH</literal> query more than once. You can override that decision by specifying <literal>MATERIALIZED</literal> to force separate calculation of the <literal>WITH</literal> query, or by specifying <literal>NOT MATERIALIZED</literal> to force it to be merged into the parent query. The latter choice risks duplicate computation of the <literal>WITH</literal> query, but it can still give a net savings if each usage of the <literal>WITH</literal> query needs only a small part of the <literal>WITH</literal> query's full output. しかし、WITH問い合わせが非再帰で副作用がない(つまり、揮発性(volatile)の関数を含まないSELECTである)場合は、親問い合わせに組み込むことができ、2つの問い合わせレベルを同時に最適化できます。 デフォルトでは、親問い合わせがWITH問い合わせを1回だけ参照する場合にこれが発生しますが、WITH問い合わせを2回以上参照する場合には発生しません。 この決定を上書きするには、MATERIALIZEDを指定してWITH問い合わせの個別の計算を強制するか、NOT MATERIALIZEDを指定して親問い合わせにマージするようにします。 後者を選択すると、WITH問い合わせの計算が重複する危険性がありますが、WITH問い合わせを使用するたびにWITH問い合わせのごく一部しか必要としない場合は、全体の節約になります。

A simple example of these rules is これらのルールの簡単な例を次に示します。

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

This <literal>WITH</literal> query will be folded, producing the same execution plan as このWITH問い合わせは組み込まれ、次のものと同じ実行計画を生成します。

SELECT * FROM big_table WHERE key = 123;

In particular, if there's an index on <structfield>key</structfield>, it will probably be used to fetch just the rows having <literal>key = 123</literal>. On the other hand, in 特に、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;

the <literal>WITH</literal> query will be materialized, producing a temporary copy of <structname>big_table</structname> that is then joined with itself &mdash; without benefit of any index. This query will be executed much more efficiently if written as この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;

so that the parent query's restrictions can be applied directly to scans of <structname>big_table</structname>. 親の問い合わせの制限をbig_tableのスキャンに直接適用することが出来ます。

An example where <literal>NOT MATERIALIZED</literal> could be undesirable is 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;

Here, materialization of the <literal>WITH</literal> query ensures that <function>very_expensive_function</function> is evaluated only once per table row, not twice. ここで、WITH問い合わせを生成すると、very_expensive_functionがテーブルの行毎に1回のみ評価され、2回は評価されないことが保証されます。

The examples above only show <literal>WITH</literal> being used with <command>SELECT</command>, but it can be attached in the same way to <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>. In each case it effectively provides temporary table(s) that can be referred to in the main command. 上の例ではSELECTを使用するWITHのみを示しています。 しかし、同じ方法でINSERTUPDATEDELETEまたはMERGEに対して付与することができます。 それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。

7.8.4. WITH内のデータ変更文 #

<title>Data-Modifying Statements in <literal>WITH</literal></title>

You can use data-modifying statements (<command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>) in <literal>WITH</literal>. This allows you to perform several different operations in the same query. An example is: 《マッチ度[86.842105]》ほとんどのデータ変更文(INSERTUPDATEDELETEは使用できますが、MERGEは使用できません)は、WITH内で使用できます。 これにより同じ問い合わせ内で複数の異なる操作を行うことができます。 《機械翻訳》WITHでは、データ-変更ステートメント(INSERTUPDATEDELETE、または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;

This query effectively moves rows from <structname>products</structname> to <structname>products_log</structname>. The <command>DELETE</command> in <literal>WITH</literal> deletes the specified rows from <structname>products</structname>, returning their contents by means of its <literal>RETURNING</literal> clause; and then the primary query reads that output and inserts it into <structname>products_log</structname>. この問い合わせは実質、productsからproducts_logに行を移動します。 WITH内のDELETEproductsから指定した行を削除し、そのRETURNING句により削除した内容を返します。 その後、主問い合わせはその出力を読み取り、それをproducts_logに挿入します。

A fine point of the above example is that the <literal>WITH</literal> clause is attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within the <command>INSERT</command>. This is necessary because data-modifying statements are only allowed in <literal>WITH</literal> clauses that are attached to the top-level statement. However, normal <literal>WITH</literal> visibility rules apply, so it is possible to refer to the <literal>WITH</literal> statement's output from the sub-<command>SELECT</command>. 上の例の見事なところは、WITH句がINSERT内のsub-SELECTではなく、INSERTに付与されていることです。 これは、データ更新文は最上位レベルの文に付与されるWITH句内でのみ許されているため必要です。 しかし、通常のWITHの可視性規則が適用されますので、sub-SELECTからWITH文の出力を参照することができます。

Data-modifying statements in <literal>WITH</literal> usually have <literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>), as shown in the example above. It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> 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 <literal>WITH</literal> lacks a <literal>RETURNING</literal> 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内のデータ変更文は通常RETURNING句(6.4を参照)を持ちます。 問い合わせの残りの部分で参照することができる一時テーブルを形成するのは、RETURNING句の出力の出力であって、データ変更文の対象テーブルではありませんWITH内のデータ変更文がRETURNING句を持たない場合、一時テーブルを形成しませんので、問い合わせの残りの部分で参照することができません。 これにもかかわらずこうした文は実行されます。 特別有用でもない例を以下に示します。

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

This example would remove all rows from tables <structname>foo</structname> and <structname>bar</structname>. The number of affected rows reported to the client would only include rows removed from <structname>bar</structname>. この例はfooテーブルとbarテーブルからすべての行を削除します。 クライアントに報告される影響を受けた行数にはbarから削除された行のみが含まれます。

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 <literal>WITH</literal>, for example: データ変更文内の再帰的な自己参照は許されません。 一部の場合において、再帰的な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);

This query would remove all direct and indirect subparts of a product. この問い合わせはある製品の直接的な部品と間接的な部品をすべて削除します。

Data-modifying statements in <literal>WITH</literal> 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 <command>SELECT</command> in <literal>WITH</literal>: as stated in the previous section, execution of a <command>SELECT</command> is carried only as far as the primary query demands its output. WITH内のデータ変更文は正確に1回のみ実行され、主問い合わせがその出力をすべて(実際にはいずれか)を呼び出したかどうかに関係なく、常に完了します。 これが、前節で説明した主問い合わせがその出力を要求した時のみにSELECTの実行が行われるというWITH内のSELECTについての規則と異なることに注意してください。

The sub-statements in <literal>WITH</literal> are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in <literal>WITH</literal>, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same <firstterm>snapshot</firstterm> (see <xref linkend="mvcc"/>), so they cannot <quote>see</quote> 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 <literal>RETURNING</literal> data is the only way to communicate changes between different <literal>WITH</literal> sub-statements and the main query. An example of this is that in WITH内の副文はそれぞれと主問い合わせで同時に実行されます。 したがってWITH内のデータ変更文を使用する時、指定したデータ変更文が実際に実行される順序は予測できません。 すべての文は同じスナップショット第13章参照)を用いて実行されます。 このため互いが対象テーブルに行った影響を見ることはできません。これは、行の更新に関する実際の順序が予測できないという影響を軽減し、RETURNINGデータが別のWITH副文と主問い合わせとの間で変更を伝える唯一の手段であることを意味します。 この例を以下に示します。

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

the outer <command>SELECT</command> would return the original prices before the action of the <command>UPDATE</command>, while in 外側のSELECTUPDATEの動作前の元々の価格を返します。

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

the outer <command>SELECT</command> would return the updated data. 一方こちらでは外側のSELECTは更新されたデータを返します。

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 <literal>WITH</literal> 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. 単一の文で同じ行を2回更新しようとすることはサポートされていません。 変更のうちの1つだけが行われますが、どれが実行されるかを確実に予測することは簡単ではありません(場合によっては不可能です)。 これはまた、同じ文内ですでに更新された行を削除する場合でも当てはまり、更新のみが実行されます。 したがって一般的には単一の文で1つの行を2回変更しようと試みることを避けなければなりません。 具体的には主文または同レベルの副文で変更される行と同じ行に影響を与えるWITH副文を記述することは避けてください。 こうした文の影響は予測することはできません。

At present, any table used as the target of a data-modifying statement in <literal>WITH</literal> must not have a conditional rule, nor an <literal>ALSO</literal> rule, nor an <literal>INSTEAD</literal> rule that expands to multiple statements. 現状、WITH内のデータ変更文の対象として使用されるテーブルはすべて、複数の文に展開される条件付きルール、ALSOルール、INSTEADルールを持ってはなりません。