PostgreSQLスキルアップノート（自己啓発のための個人サイト）
WITH問い合わせ（共通テーブル式・CTE） 更新と同時に対象行を出力
【一覧に戻る】
自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ WITH問い合わせ（共通テーブル式・CTE） 更新と同時に対象行を出力
■■■■
■■■■
■■■■ 2013/02/16
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
(C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
UPDATE-SELECT
DELETE-SELECT
UPDATE-INSERT (UPSERTではなく）
DELETE-INSERT
以上、このページ限りの造語で一般的ではありません。
イメージがわかりやすいためINSERT...SELECT文にならって記載しました。ご注意下さい。
【マニュアル】
WITH問い合わせ（共通テーブル式）<"http://www.postgresql.jp/document/9.2/html/queries-with.html#QUERIES-WITH-MODIFYING" target="_blank">→●[マニュアル]
【参考記事】
−
■■ 概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
WITH問い合わせの副文でUPDATEやDELETE文などを使うことにより、更新・削除と同時に
その対象となった行を表示させたり別のテーブルに出力したりすることができる。
また副文のDELETEと主問い合わせのINSERT-SELECT文を組み合わせることで、テーブル間の
行移動を一度に実行することができる。
なお、副文と主問い合わせで更新する対象行が重なってはいけないなど、いくつか注意点がある。
詳細はマニュアル参照のこと。
■■ 事前準備
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】テスト用テーブルの作成
復習のためにテストデータを再帰のWITH問い合わせを使って作成。
本当は連番データはgenerate_series関数を使用した方が簡単にできる。
drop table t1;
create table t1(c1 int primary key,c2 text);
--
with recursive test_data(c1,c2) as (
select 1,null
union all
select c1+1,null from test_data where c1+1 <= 10
)
insert into t1 select * from test_data;
【□】テストデータの内容
select * from t1;
database1=# select * from t1;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
(10 行)
■１■ 更新と同時に対象となった行を表示 【UPDATE-SELECT】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 更新＆内容表示
WITH upd_info AS (
UPDATE t1 SET c2 = 'AAAAA' WHERE c1 > 5
RETURNING *
)
SELECT * FROM upd_info;
database1-# SELECT * FROM upd_info;
c1 | c2
----+-------
6 | AAAAA
7 | AAAAA
8 | AAAAA
9 | AAAAA
10 | AAAAA
(5 行)
以上のように更新の対象となった行のみ更新後の内容で表示される。
【参考】
仮に、最後の SELECT * FROM upd_info
の代わりに SELECT * FROM t1
とすると、更新後のt1テーブルが表示されるわけではなく
更新前のテーブル内容が表示されることになるので注意。
【□】 更新後の全テーブルデータを確認
SELECT * FROM t1;
database1=# SELECT * FROM t1;
c1 | c2
----+-------
1 |
2 |
3 |
4 |
5 |
6 | AAAAA
7 | AAAAA
8 | AAAAA
9 | AAAAA
10 | AAAAA
(10 行)
■２■ 削除と同時に対象となった行を表示 【DELETE-SELECT】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
基本的には前項のUPDATEと同じ。
DELETEの実行とともにDELETEした行を表示することができる。
（以下は前項から引き続いて実施したときの結果内容になっています）
【□】 削除＆内容表示
WITH del_info AS (
DELETE from t1 WHERE c1 > 5
RETURNING *
)
SELECT * FROM del_info;
c1 | c2
----+-------
6 | AAAAA
7 | AAAAA
8 | AAAAA
9 | AAAAA
10 | AAAAA
(5 行)
【□】 削除後の全テーブルデータを確認
SELECT * FROM t1;
database1=# SELECT * FROM t1;
c1 | c2
----+----
1 |
2 |
3 |
4 |
5 |
(5 行)
■３■ 行を別テーブルへ移動 【DELETE-INSERT】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
次項とほとんど重なってしまったため内容は削除。
以下のような記述で簡単に別テーブルへ行を移動させることができる。
【参考】
WITH tmpinfo AS (
DELETE FROM t1 WHERE 〜条件
RETURNING *
)
INSERT INTO 移動先テーブル SELECT * FROM tmpinfo;
■４■ 更新削除した内容を別テーブルへ記録
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
行の更新や削除を行い、行の内容を別のテーブルへ記録する。
履歴テーブルという想定なので、カラムにはトランザクションの実行時刻とユーザ名などを追加。
★注意★ トリガーなど、他に効果的な手段もあるのでご注意下さい。
【□】 冒頭の事前準備の項をもう一度実行してt1テーブルを再作成する。
【□】 履歴用テーブルt1_rirekiを以下により作成する。
drop table t1_rireki;
create table t1_rireki(
ex_date timestamp , -- 更新時刻格納用
ex_user varchar(10), -- 更新ユーザ名格納用
ex_cmd varchar(6) , -- 更新コマンド（UPDATE,DELETE）格納用
c1 int,
c2 text
);
１．更新して（更新後の）内容を履歴へ 【UPDATE-INSERT】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 更新の実行
WITH tmpinfo AS (
UPDATE t1 SET c2 = 'AAAAA' WHERE c1 > 5
RETURNING *
)
INSERT INTO t1_rireki SELECT current_timestamp,current_user,'UPDATE', * FROM tmpinfo;
【□】 履歴の確認
select * from t1_rireki;
database1=# select * from t1_rireki;
ex_date | ex_user | ex_cmd | c1 | c2
----------------------------+---------+--------+----+-------
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 6 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 7 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 8 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 9 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 10 | AAAAA
(5 行)
【□】 元のテーブルt1の確認
select * from t1;
database1=# select * from t1;
c1 | c2
----+-------
1 |
2 |
3 |
4 |
5 |
6 | AAAAA
7 | AAAAA
8 | AAAAA
9 | AAAAA
10 | AAAAA
(10 行)
２．削除して内容を履歴へ 【DELETE-INSERT】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 更新の実行
WITH tmpinfo AS (
DELETE FROM t1 WHERE c1 > 8
RETURNING *
)
INSERT INTO t1_rireki SELECT current_timestamp,current_user,'DELETE', * FROM tmpinfo;
【□】 履歴の確認
select * from t1_rireki;
database1=# select * from t1_rireki;
ex_date | ex_user | ex_cmd | c1 | c2
----------------------------+---------+--------+----+-------
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 6 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 7 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 8 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 9 | AAAAA
2013-02-16 13:28:12.511783 | dba01 | UPDATE | 10 | AAAAA
2013-02-16 13:28:49.428922 | dba01 | DELETE | 9 | AAAAA ★今回
2013-02-16 13:28:49.428922 | dba01 | DELETE | 10 | AAAAA ★今回
(7 行)
【□】 元のテーブルt1の確認
select * from t1;
database1=# select * from t1;
c1 | c2
----+-------
1 |
2 |
3 |
4 |
5 |
6 | AAAAA
7 | AAAAA
8 | AAAAA
(8 行)
以上
