PostgreSQL-WITH句

PostgreSQLでは、WITHクエリは、より大きなクエリで使用するための補助ステートメントを作成する方法を提供します。複雑で大きなクエリを、読みやすい単純な形式に分解するのに役立ちます。これらのステートメントは、共通テーブル式またはCTEと呼ばれることが多く、1つのクエリに対してのみ存在する一時テーブルを定義するものと考えることができます。

CTEクエリであるWITHクエリは、サブクエリが複数回実行される場合に特に役立ちます。一時テーブルの代わりにも同様に役立ちます。集計を1回計算し、クエリでその名前（複数回の場合もあります）で参照できるようにします。

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はそれ自体を参照できます。つまり、WITHクエリはそれ自体の出力を参照できるため、再帰という名前が付けられています。

例

次のようなレコードを持つテーブルCOMPANYについて考えてみます。

testdb# 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;

上記のPostgreSQLステートメントは次の結果を生成します-

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句を使用してクエリを記述し、20000未満の給与の合計を求めます。

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

上記のPostgreSQLステートメントは次の結果を生成します-

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

以下に示すように、WITH句とともにデータ変更ステートメントを使用してクエリを記述しましょう。

まず、テーブルCOMPANYと同様のテーブルCOMPANY1を作成します。この例のクエリは、行をCOMPANYからCOMPANY1に効果的に移動します。WITHのDELETEは、指定された行をCOMPANYから削除し、RETURNING句を使用してその内容を返します。次に、プライマリクエリがその出力を読み取り、COMPANY1 TABLE −に挿入します。

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

上記のPostgreSQLステートメントは次の結果を生成します-

INSERT 0 3

ここで、COMPANYテーブルとCOMPANY1テーブルのレコードは次のとおりです。

testdb=# 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)


testdb=# 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)