PostgreSQL-HowTO01


更新可能なVIEWの作成方法(2004.12.12 執筆)

あらまし

たまたま会社で相談を受け話しを聞くと
「複数のテーブルに対して検索(select)や更新(update)を頻繁に行っている処理を工夫したい。」
という。
VIEWについては、まだ使ったことがなかったらしく、VIEWの特徴を教えてあげると
「それ便利!」
と、大喜び。
しかし、直後に
「これ(VIEW)にupdateするとエラーでるよ。」
と・・・。
ん~~、ドキュメント読んでないな^^;と思いつつ、説明ついでにこのページを作成。
もし、これからPostgreSQLを始めたり、VIEWについて勉強したいという方にお薦めです。

VIEWってなに?

まずVIEWについてどんな特徴があるのか知らないとお話にならないので
PostgreSQLの日本公式サイトに載っているcreate viewの説明を参考にでもしてください。

簡単に言ってしまえば、元テーブル(単独・複数)から必要な項目を抜粋した仮想的な参照用のテーブルをVIEWと言う。
と言えば初めての人でも分かるのかな?(笑

デモ用のテーブル・データを用意する

ここでは仮に2つのテーブルを用意します。
 ○「t1」として会員IDと金額を保存できるテーブル
 ○「t2」として会員IDと銀行名を保存できるテーブル
table01.png

■サンプルテーブル「t1」定義

  1
  2
  3
  4
mysql> create table t1 (
    id    varchar(12) not null PRIMARY KEY,
    money    int
     );

■サンプルテーブル「t2」定義

  1
  2
  3
  4
mysql> create table t2 (
    id    varchar(12) not null PRIMARY KEY,
    bank    varchar(64) NOT NULL
     );

■テーブル「t1」にダミーデータ挿入

  1
  2
  3
mysql> insert into t1 values('A0001', 10000);
mysql> insert into t1 values('A0002', 50000);
mysql> insert into t1 values('A0003', 0);

■テーブル「t2」にダミーデータ挿入

  1
  2
mysql> insert into t2 values('A0001', 'UFJ');
mysql> insert into t2 values('A0002', 'CitiBank');

■テーブル「t1」の内容確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from t1;
   id   | money
 -------+-------
  A0001 | 10000
  A0002 | 50000
  A0003 |     0
 (3 rows)

■テーブル「t2」の内容確認

  1
  2
  3
  4
  5
  6
mysql> select * from t2;
   id   |   bank
 -------+----------
  A0001 | UFJ
  A0002 | CitiBank
 (2 rows)

VIEWの定義

さてここでVIEWの定義です。
用途に合わせて必要な項目を列記してください。
ここではテーブル「t1」の会員ID(id)と金額(money)
テーブル「t2」の銀行名(bank)をVIEW「v1」として定義します。
view01.png

■VIEW「v1」の作成

  1
mysql> create view v1 as select t1.id, t1.money, t2.bank from t1 left join t2 on t1.id = t2.id;

■VIEW「v1」の内容確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |   bank
 -------+-------+----------
  A0001 | 10000 | UFJ
  A0002 | 50000 | CitiBank
  A0003 |     0 | 
 (3 rows)

■テーブル「t1」にデータ更新してView[v1]の内容を確認

  1
mysql> update t1 set money=30000 where id='A0001';

□まずテーブル「t1」の内容を確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from t1;
   id   | money
 -------+-------
  A0002 | 50000
  A0003 |     0
  A0001 | 30000
 (3 rows)

□つぎにVIEW「v1」の内容を確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |   bank
 -------+-------+----------
  A0001 | 30000 | UFJ
  A0002 | 50000 | CitiBank
  A0003 |     0 | 
 (3 rows)

□VIEW「v1」もちゃんと更新されているのが分かる

VIEWに対して更新可能にするルールを定義する

■VIEW「v1」に対して更新[update]を実行するためのルールを定義する
 基本的にVIEWは読み取り専用です。
 挿入、更新、削除を許可していません。

 そこでルールを作成することで更新可能なVIEWと同じ効果を得ることができます。
 見た目はVIEWに更新するSQLを使用しますが、ルールによって実際には参照元のTABLEを操作しています。

■VIEW更新用のルール定義
□VIEW「v1」の「money」を更新するためのルールを定義

  1
  2
mysql> create rule v1write_money as on update to v1 do instead (update t1 set money = new.money where id = old.id);
        -------------*1     ------*2  --*3          ---------------------------------------------------*4

 *1:ルール名を「v1write_money」として任意につける。
 *2:ルールの適用コマンドは「update」に対して発動させる。
 *3:ルールの適用先はVIEW「v1」に対して。
 *4:項目「money」はテーブル「t1」が参照元になるため、instead 以降に テーブル「t1」の「money」を更新(update)するSQLを書く。

□同様にVIEW「v1」の「bank」を更新するためのルールを定義

  1
  2
mysql> create rule v1write_bank as on update to v1 do instead (update t2 set bank = new.bank where id = old.id);
        ------------*1     ------*2  --*3          -------------------------------------------------*4

 *1:ルール名を「v1write_bank」として任意につける。
 *2:ルールの適用コマンドは「update」に対して発動させる。
 *3:ルールの適用先はVIEW「v1」に対して。
 *4:項目「bank」はテーブル「t2」が参照元になるため、instead 以降に テーブル「t2」の「bank」を更新(update)するSQLを書く。

ルール確認

■定義したルールの確認方法

  1
  2
  3
  4
  5
  6
mysql> select * from pg_rules;
  schemaname |  tablename  |   rulename    |                                                                     definition
 ------------+-------------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------
  public     | v1          | v1write_bank  | CREATE RULE v1write_bank AS ON UPDATE TO v1 DO INSTEAD UPDATE t2 SET bank = new.bank WHERE ((t2.id)::text = (old.id)::text);
  public     | v1          | v1write_money | CREATE RULE v1write_money AS ON UPDATE TO v1 DO INSTEAD UPDATE t1 SET money = new.money WHERE ((t1.id)::text = (old.id)::text);
 (2 rows)

■設定したルールが働くか確認
■VIEW「v1」の「money」に対して更新を行う

  1
mysql> update v1 set money=1000 where id='A0001';

□VIEW「v1」の内容確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |   bank
 -------+-------+----------
  A0002 | 50000 | CitiBank
  A0003 |     0 | 
  A0001 | 1000  | UFJ
 (3 rows)

■同様にVIEW「v1」の「bank」に対して更新を行う

  1
mysql> update v1 set bank='TokyoMitsubishi' where id='A0001';

□VIEW「v1」の内容確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |      bank
 -------+-------+-----------------
  A0002 | 30000 | CitiBank
  A0003 |     0 | 
  A0001 |  1000 | TokyoMitsubishi
 (3 rows)

■上記定義した2つのルールを一つにまとめた場合(「money」「bank」両方を更新できるルールを定義)

  1
  2
mysql> create rule v1write_all as on update to v1 do instead (update t1 set money = new.money where id = old.id;update t2 set bank = new.bank where id = old.id);
        -----------*1     ------*2  --*3          -------------------------------------------------*4 -----------------------------------------------*5

 *1:ルール名を「v1write_all」として任意につける。
 *2:ルールの適用コマンドは「update」に対して発動させる。
 *3:ルールの適用先はVIEW「v1」に対して。
 *4:項目「money」はテーブル「t1」が参照元になるため、instead 以降に テーブル「t1」の「money」を更新(update)するSQLを書く。
 *5:項目「bank」はテーブル「t2」が参照元になるため、instead 以降に テーブル「t2」の「bank」を更新(update)するSQLを書く。
 ※複数のSQL文を並べるため";"で個々につけて括弧()で結ぶ。

■設定したルールが働くか確認
■VIEW「v1」の「money」「bank」両方に対して更新を行う

  1
mysql> update v1 set money=0,bank='MitsuiSumitomo' where id='A0001';

□VIEW「v1」の内容確認

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |      bank
 -------+-------+----------------
  A0002 | 30000 | CitiBank
  A0003 |     0 | 
  A0001 |     0 | MitsuiSumitomo
 (3 rows)

■更新の必要がある項目に対してルールで更新用SQLを定義してあげれば、VIEWでもテーブルと同様な更新(update)が可能になる。

注意!

■上記の例では、VIEW「v1」上では会員ID[A0003]の銀行名「bank」が未入力になっています。
通常TABLEに対しては更新(update)で銀行名を更新登録しますが、

  1
mysql> update v1 set bank='UFJ' where id='A0003';

とすると処理的には通りますが、実際にVIEW「v1」のデータを表示してみると反映されていません。

  1
  2
  3
  4
  5
  6
  7
mysql> select * from v1;
   id   | money |      bank
 -------+-------+----------------
  A0002 | 30000 | CitiBank
  A0001 |     0 | MitsuiSumitomo
  A0003 |     0 | 
 (3 rows)

これまで説明したVIEWの仕組みを理解できた方は、理由が分かりますよね?
VIEW「v1」はあくまで仮想です。
実際に銀行名のデータを更新するのはTABLE「t2」上になります。
実際には定義したルールによって

  1
mysql> update t2 set bank = 'UFJ' where id = 'A0003';

が実行されているのです。

TABLE「t2」上にはまだ会員ID「A0003」のデータが存在していないため、更新(update)が効かないのです。

こういう場合を考慮したルールも追加して登録する必要があります。

その他VIEW・ルールに使用するSQL

■ルールの削除

  1
mysql> drop rule v1write_all on v1;

■VIEWの削除

  1
mysql> drop view v1;


添付ファイル: fileview01.png 152件 [詳細] filetable01.png 150件 [詳細]

Last-modified: 2015-04-22 (水) 17:16:37 (947d)