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_sales、top_regionsという名前の２つの補助文を定義します。 ここで、regional_salesの出力はtop_regions内で使用され、top_regionsはSELECT主問い合わせで使用されます。 この例は WITHなしでも記述できますが、二階層の入れ子のsub-SELECTを必要とします。この方法に従うほうが多少扱いやすいです。

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）です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。 このような問い合わせは以下のように実行されます。

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

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

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

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問い合わせによりたとえ１回以上参照されるとしても、通常は親問い合わせ実行で１回だけ評価されることです。 したがって、複数の場所で必要な高価な計算は、冗長作業を防止するため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がテーブルの行毎に１回のみ評価され、２回は評価されないことが保証されます。

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のみを示しています。 しかし、同じ方法でINSERT、UPDATE、DELETEまたはMERGEに対して付与することができます。 それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。

You can use most data-modifying statements (<command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>, but not <command>MERGE</command>) in <literal>WITH</literal>. This allows you to perform several different operations in the same query. An example is: ほとんどのデータ変更文（INSERT、UPDATE、DELETEは使用できますが、MERGEは使用できません）は、WITH内で使用できます。 これにより同じ問い合わせ内で複数の異なる操作を行うことができます。

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内のDELETEはproductsから指定した行を削除し、その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内のデータ変更文は正確に１回のみ実行され、主問い合わせがその出力をすべて（実際にはいずれか）を呼び出したかどうかに関係なく、常に完了します。 これが、前節で説明した主問い合わせがその出力を要求した時のみに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 外側のSELECTはUPDATEの動作前の元々の価格を返します。

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