PostgreSQL WITH句

PostgreSQLでは、WITH句は、より大きなクエリ内で使用するための補助文を作成する方法を提供します。

WITH句は、複雑で大きなクエリをより単純な形式に分割して読みやすくするのに役立ちます。これらの文は通常、共通テーブル式（Common Table Expression、CTE）と呼ばれ、クエリのために存在する一時テーブルと見なすこともできます。

WITH句は、サブクエリを複数回実行する場合に特に便利で、クエリ内でその名前を使用して（複数回でも）参照できます。

WITH句は使用する前に定義しておく必要があります。

構文

WITHクエリの基本構文は次のとおりです。

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

name_for_summary_dataはWITH句の名前です。name_for_summary_data既存のテーブル名と同じ名前を使用でき、優先されます。

WITH内ではINSERT、UPDATE、DELETE文を使用でき、同じクエリ内で複数の異なる操作を実行できます。

WITH再帰

WITH句内では、自身が出力したデータを使用できます。

共通テーブル式（CTE）には、自身を参照できるという重要な利点があり、再帰CTEを作成できます。再帰CTEは、完全な結果セットを取得するまで初期CTEを繰り返し実行し、データのサブセットを返す共通テーブル式です。

例

COMPANYテーブルを作成します（COMPANY SQLファイルをダウンロード）。データ内容は次のとおりです。

サンプルdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次に、WITH句を使用して上記のテーブルからデータをクエリします。

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

結果は次のとおりです。

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

次に、使用しましょうRECURSIVEキーワードとWITH句を使用してクエリを作成し、検索しますSALARY（給与）フィールドが20000未満のデータを検索し、それらの合計を計算します：

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

結果は次のとおりです。

 sum
-------
 25000
(1 row)

次に、COMPANYテーブルと似たCOMPANY1テーブルを作成し、DELETE文とWITH句を使用してCOMPANYテーブルから削除しますSALARY（給与）フィールドが30000以上のデータを削除し、削除したデータをCOMPANY1テーブルに挿入して、COMPANYテーブルのデータをCOMPANY1テーブルに移動します。

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);


WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

結果は次のとおりです。

INSERT 0 3

このとき、COMPANYテーブルとCOMPANY1テーブルのデータは次のとおりです。

サンプルdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


サンプルdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)