LoginSignup
13
8

More than 3 years have passed since last update.

更新可能なビューの作成方法(PostgreSQL)

Last updated at Posted at 2019-10-28

更新可能なビューとは

 更新可能なビューとは一般的なテーブルのように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

13
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
13
8