PostgreSQL/SQL/WITH句の変更点

追加された行はこの色です。
削除された行はこの色です。
PostgreSQL/SQL/WITH句へ行く。
PostgreSQL/SQL/WITH句の差分を削除

* 共通表式 WITH句 [#bfc0ad13]


** 前提 [#r77ef48b]

 SELECT * FROM t1;
  id1 | id2
 -----+-----
    1 |   2
    3 |   4
    5 |   6
 (3 rows)

 SELECT * FROM t2;
  id
 ----
   1
   3
 (2 rows)

** WITHを使わない [#a79e0c4e]
 SELECT * FROM t1 WHERE id1 IN (SELECT id FROM t2 WHERE id > 1) OR id2 IN (SELECT id FROM t2 WHERE id > 1);

  id1 | id2
 -----+-----
    3 |   4
 (2 rows)

** WITHを使う [#k66c1e45]
 WITH _t2 AS (SELECT id FROM t2 WHERE id > 1) 
 SELECT * FROM t1 WHERE id1 IN (SELECT id FROM _t2) OR id2 IN (SELECT id FROM _t2);

  id1 | id2
 -----+-----
    3 |   4
 (1 row)

** WITHでDELETE [#y5a0de08]
 SELECT * FROM t1;

  id
 -----
  100
  200
 (2 rows)

 SELECT * FROM t1_backup ;

  id
 ----
 (0 rows)

 WITH del AS (DELETE FROM t1 RETURNING id) INSERT INTO t1_backup SELECT * FROM del;

 SELECT * FROM t1;

  id
 ----
 (0 rows)

 SELECT * FROM t1_backup ;

  id
 -----
  100
  200
 (2 rows)

** 参考 [#x511c25a]
http://lets.postgresql.jp/documents/technical/with_recursive