ビューの管理

データベース設計

Lis Sulmont

Curriculum Manager

より複雑なビューの作成

集計: SUM()、AVG()、COUNT()、MIN()、MAX()、GROUP BY、など
結合: INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN
条件文: WHERE、HAVING、UNIQUE、NOT NULL、AND、OR、>、<、など

ビューへのアクセスの付与と取り消し

GRANT privilege(s)またはREVOKE privilege(s)

ON object

TO roleまたは FROM role

権限: SELECT、INSERT、UPDATE、DELETEなど
オブジェクト: テーブル、ビュー、スキーマなど
ロール: データベースユーザーまたはデータベースユーザーのグループ

付与と取り消しの例

GRANT UPDATE ON ratings TO PUBLIC;

REVOKE INSERT ON films FROM db_user;

ビューの更新

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

すべてのビューが変更可能とは限らない

ビューは1つのテーブルで構成される
ウィンドウ関数や集計関数を使用しない

¹ https://www.postgresql.org/docs/9.5/sql-update.html

ビューへの挿入

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

すべてのビューが挿入可能とは限らない

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

ビューへの挿入

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

すべてのビューが挿入可能とは限りません

要点: ビューを通じてデータを変更しない

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

ビューの削除

DROP VIEW view_name [ CASCADE | RESTRICT ];

RESTRICT（デフォルト）：ビューに依存するオブジェクトがある場合はエラーを返す
CASCADE: ビューとそのビューに依存するすべてのオブジェクトを削除する

ビューの再定義

CREATE OR REPLACE VIEW view_name AS new_query

view_name が存在する場合は、置き換えられる
new_query は、古いクエリと同じ列名、順序、データ型を生成する必要がある
列の出力は異なる場合がある
新しい列は末尾に追加できる

これらの条件を満たせない場合は、既存のビューを削除して新しいビューを作成する

¹ https://www.postgresql.org/docs/9.2/sql-createview.html

ビューの変更

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

¹ https://www.postgresql.org/docs/9.2/sql-alterview.html

練習しましょう！

データベース設計