7.8. WITH問い合わせ（共通テーブル式）

7.8.1. 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;

これは地域（region）販売トップのみの製品ごとの売上高を表示します。 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問い合わせの汎用形式は常に、非再帰的表現（non-recursiveterm）、そしてUNION（またはUNION ALL）、そして再帰的表現（recursive term）です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。 このような問い合わせは以下のように実行されます。

上記の例で、作業テーブルはそれぞれの手順での単なる単一行で、引き続く作業で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
    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

再帰的問い合わせを扱う場合、問い合わせの再帰部分が時としてタプルを返さない、または、問い合わせが永久にループするといったことのないように注意することが重要です。 たまには、UNION ALLの替わりにUNIONを使用し、重複する前回の出力行を廃棄することで、これを実現できます。 しかし、ある周期でしばしば完全に重複している出力行を含みません。 同じ場所が既に到達されたかどうかを確認するために、１つだけ、または数フィールドを検査する必要があるかもしれません。 このような状態を取り扱う標準手法は、既に巡回された値の配列を計算することです。 例えば、linkフィールドを使ってテーブル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
)
SELECT * FROM search_graph;

この問い合わせはlink関係が循環を含んでいればループします。 "depth"出力が必要ですので、UNION ALLをUNIONに変えるだけでは、ループを取り除くことができません。 その代わり、linkの特定の経路をたどっている間、同じ列に到達したかどうかを認識する必要があります。 このループしやすい問い合わせに、pathとcycleの２列を加えます。

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

巡回防止は別として、配列値は"path"がどんな特定行に到達したかの表示としての公正さからしばしば利用価値があります。

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

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

ループするかどうか確信が持てない問い合わせをテストする有益な秘訣として、親問い合わせに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問い合わせは一般的に、親問い合わせが後で破棄するであろう行を抑制せずに、書かれた通りに評価されます。 （しかし、上で述べたように、問い合わせの参照が限定された数の行のみを要求する場合、評価は早期に停止します。）

上の例ではSELECTを使用するWITHのみを示しています。 しかし、同じ方法でINSERT、UPDATE、またはDELETEを付与することができます。 それぞれの場合において、これは主コマンド内で参照可能な一時テーブルを実質的に提供します。

7.8.2. WITH内のデータ変更文

WITH内でデータ変更文（INSERT、UPDATE、DELETE）を使用することができます。 これにより同じ問い合わせ内で複数の異なる操作を行うことができます。

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に挿入します。

上の例の見事なところは、INSERT内の副SELECTではなくWITH句がINSERTに付与されていることです。 これは、データ更新文は最上位レベルの文に付与されるWITH句内でのみ許されているため必要です。 しかし、通常のWITHの可視性規則が適用されますので、副SELECTからWITH文の出力を参照することができます。

上の例で示したように、WITH内のデータ変更文は通常RETURNING句を持ちます。 データ変更文の対象テーブルではなく、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内のデータ変更文は正確に１回のみ実行され、主問い合わせがその出力をすべて（実際にはいずれか）を呼び出したかどうかに関係なく、常に完了します。 これが、前節で説明した主問い合わせがその出力を要求した時にSELECTの実行が行われるというWITH内の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ルールを持ってはなりません。