更新可能なビューの作成方法（PostgreSQL）

更新可能なビューとは

　更新可能なビューとは一般的なテーブルのようにUPDATE文やDELETE文、INSERT文を実行することができるビューのことです。ビューを更新するとビューの元のテーブルも更新されます。今回の記事では、(1)更新可能なビューの作り方と、(2)更新可能ビューを作成する上での注意点、(3)更新可能なビューかを確認する方法を説明します。

(1)更新可能なビューの作り方

　更新可能なビューは普通のビュー作成のSQL文を打つだけで大丈夫です。ただし、(2)で説明するようにSELECT文によっては更新することができないビューになります。

書き方：CREATE VIEW ビュー名 AS (SELECT文)

postgres=# create view test_view as select name from test_table ;    --ビューを作成
CREATE VIEW

　実際に更新できるのかを確認しておきましょう。

postgres=# select * from test_view ;         --更新前のビューを確認
  name
--------
 suzuki
 ito
 sato
(3 rows)

postgres=# update test_view set name = 'kato' where name = 'sato';    --ビューを更新する
UPDATE 1

postgres=# select * from test_view ;         --更新後のビューを確認
  name
--------
 suzuki
 ito
 kato
(3 rows)

(2)更新可能なビューを作成する上での注意点

　最初に述べたように、更新可能なビューを更新すると、ビューの元となるテーブルも更新します。したがって、ビューを更新するときには、更新対象のビューだけでなく、更新対象のテーブルのデータも明確になる（テーブルのデータとビューのデータが1対1に対応している）必要があります。そのため以下のようにビューを定義している場合、更新可能なビューにはなりません（雑に書いているので厳密なことを知りたい場合は参考のURLを確認してください）。

・SELECTのFROM句に複数のテーブルが指定してある
・UNION、EXCEPT、INTERSECTを使用している
・DISTINCT、GROUP BY、HAVING、LIMIT、OFFSETを使用している
・式や関数（SUMなどの集約関数）を含んでいない

　上記のときには、どのテーブルを更新してよいのかがわからなくなってしまいます。例えば、以下のようにUNIONを使って、ビューを作成しているときを考えてみましょう。仮にビューのidが3のところの'kato'を'tanaka'にUPDATEしようとした場合、test_tableのnameを更新すればよいのか、test_table2のnameを更新すればよいのか、はたまたtest_tableとtest_tableの両方を更新すればよいのかがデータベース側にはわかりません。

postgres=# select * from test_table;
 id |  name
----+--------
  1 | suzuki
  2 | ito
  3 | kato
(3 rows)

postgres=# select * from test_table2;
 id |   name
----+----------
  3 | kato
  4 | aoi
  5 | kito
  6 | watanabe
(4 rows)

postgres=# create view test_view2 as select * from test_table union select * from test_table2 order by id asc ;
CREATE VIEW

postgres=# select * from test_view2;
 id |   name
----+----------
  1 | suzuki
  2 | ito
  3 | kato
  4 | aoi
  5 | kito
  6 | watanabe
(6 rows)

(3)更新可能なビューかを確認する方法

　更新可能なビューかどうかは情報スキーマのviewsビューを確認することでわかります。is_updatableがYESならばUPDATEとDELETEが可能であることを意味し、is_insertable_intoがYESならばINSERTが可能であることを意味します。

書き方：SELECT * FROM information_schema.views WHERE table_name = 'ビュー名';

postgres=# select * from information_schema.views where table_name = 'test_view';
-[ RECORD 1 ]--------------+------------------------
table_catalog              | postgres
table_schema               | public
table_name                 | test_view
view_definition            |  SELECT test_table.name+
                           |    FROM test_table;
check_option               | NONE
is_updatable               | YES                        --UPDATEとDELETE可能
is_insertable_into         | YES                        --INSERT可能
is_trigger_updatable       | NO
is_trigger_deletable       | NO
is_trigger_insertable_into | NO

まとめ

　ビューの定義の方法によって、更新可能なビューか更新不可能なビューになるかが決まります。
　読んでくださり、ありがとうございました。

参考

PostgreSQL11のマニュアル（CREATE VIEW）
https://www.postgresql.jp/document/11/html/sql-createview.html
Let's Postgres, 「PostgreSQL 9.3の新機能: 更新可能VIEW」
https://lets.postgresql.jp/documents/technical/9.3/updatable_view/1