CREATE VIEW — 新しいビューを定義する
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEWname
[ (column_name
[, ...] ) ] [ WITH (view_option_name
[=view_option_value
] [, ... ] ) ] ASquery
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
CREATE VIEW
は問い合わせによるビューを定義します。
ビューは物理的な実体として存在するものではありません。
その代わり、問い合わせでビューが参照される度に、指定された問い合わせが実行されます。
CREATE OR REPLACE VIEW
も同様の働きをしますが、
このコマンドでは、同じ名前のビューが既に存在している場合、そのビューを置き換えます。
新しい問い合わせは、既存のビュー問い合わせが生成する列と同じ列(つまり、同じ順序の同じデータ型の同じ列名)を生成しなければなりません。
しかし、そのリストの最後に列を追加しても構いません。
出力列を生成する計算をまったく異なるものにしても構いません。
スキーマ名が付けられている場合(例えば、CREATE VIEW myschema.myview ...
)、ビューは指定されたスキーマに作成されます。
スキーマ名がなければ、そのビューは現在のスキーマに作成されます。
一時ビューは特別なスキーマに作成されます。
そのため、一時ビューを作成する時にはスキーマ名を付けることはできません。
ビュー名は、同じスキーマ内の他のビュー、テーブル、シーケンス、インデックス、外部テーブルとは異なる名前である必要があります。
TEMPORARY
またはTEMP
これが指定された場合、ビューは一時ビューとして作成されます。 現在のセッションが終わった時、一時ビューは自動的に削除されます。 一時ビューが存在する間、現在のセッションでは、これと同じ名前の永続リレーションはスキーマ修飾した名前で参照していない限り不可視です。
ビューで参照されるテーブルの一部が一時テーブルであった場合、(TEMPORARY
の指定があってもなくても)ビューは一時ビューとして作成されます。
RECURSIVE
再帰的ビューを作成します。
CREATE RECURSIVE VIEW [schema
. ]view_name
(column_names
) AS SELECT...
;
という構文は
CREATE VIEW [schema
. ]view_name
AS WITH RECURSIVEview_name
(column_names
) AS (SELECT...
) SELECTcolumn_names
FROMview_name
;
と同等です。 再帰的ビューではビューの列名リストを指定する必要があります。
name
作成するビューの名前です(スキーマ修飾名も可)。
column_name
ビューの列名として使用する名前のリストで、省略可能です。省略された場合、問い合わせに由来する名前が使用されます。
WITH ( view_option_name
[= view_option_value
] [, ... ] )
この句はビュー用のオプションのパラメータを指定します。 以下のパラメータがサポートされています。
check_option
(string
)このパラメータはlocal
かcascaded
のいずれかで、WITH [ CASCADED | LOCAL ] CHECK OPTION
を指定するのと同じです(以下を参照)。
このオプションは、既存のビューについてALTER VIEWを使って変更することができます。
security_barrier
(boolean
)行単位セキュリティを提供することを意図したビューでは、これを有効にしなければなりません。 詳細については39.5. ルールと権限を参照してください。
query
WITH [ CASCADED | LOCAL ] CHECK OPTION
このオプションは、自動的に更新可能なビューの動作を制御します。
このオプションが指定された場合、ビューに対するINSERT
およびUPDATE
コマンドでは、新しい行がビュー定義の条件を満たすことが検査されます(つまり、新しい行がビューで見ることができるかどうか、検査されます)。
条件を満たさない場合、更新は拒絶されます。
CHECK OPTION
が指定されない場合、ビューに対するINSERT
およびUPDATE
コマンドは、ビューで見ることができない行を作ることができます。
以下のcheck optionがサポートされます。
LOCAL
新しい行は、そのビュー自体に直接定義されている条件に対してのみ検査されます。
ビューが基にするビューについて定義されている条件は、(それらもCHECK OPTION
を指定しているのでなければ)検査されません。
CASCADED
新しい行は、そのビュー、およびそれが基にするすべてのビューの条件に対して検査されます。
CHECK OPTION
が指定され、LOCAL
もCASCADED
も指定されていないときは、CASCADED
が指定されたとみなされます。
CHECK OPTION
はRECURSIVE
なビューで使うことはできません。
CHECK OPTION
は、自動更新可能で、かつINSTEAD OF
トリガーもINSTEAD
ルールもないビューについてのみサポートされていることに注意してください。
自動更新可能ビューがINSTEAD OF
トリガーのあるビューに基づいて定義されている場合、LOCAL CHECK OPTION
を使って自動更新可能ビューの条件を検査することはできますが、INSTEAD OF
トリガーを持つ基のビューの条件は検査されません(cascaded check optionはトリガーで更新されるビューにまでは伝わらず、またトリガーで更新可能なビューに直接定義されたcheck optionは無視されます)。
ビューあるいはその基となるリレーションにINSTEAD
ルールがあり、INSERT
あるいはUPDATE
の書き換えが生じる場合、その書き換えられたクエリでは(INSTEAD
ルールのあるリレーションに基づく自動更新可能ビューのものも含めて)すべてのcheck optionが無視されます。
ビューを削除するには、DROP VIEW文を使用してください。
ビューの列の名前と型は指定通りに割り当てられることに注意してください。 例えば、次のコマンドを見てください。
CREATE VIEW vista AS SELECT 'Hello World';
この例は、次の2点から好ましくありません。
まず、列の名前がデフォルトの?column?
になります。さらに、列のデータ型もデフォルトのunknown
になってしまいます。
ビューの結果として文字リテラルを返したい場合は、次のように指定してください。
CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
ビューが参照するテーブルにアクセスできるかどうかは、ビューの所有者の権限で決定されます。 これは、場合によっては、背後のテーブルに対する安全で制限されたアクセスを提供します。 しかしすべてのビューが不正な改変に対して安全ではありません。 39.5. ルールと権限を参照してください。 ビュー内で実行される関数については、ビューを使用した問い合わせにおいて、その関数が直接呼び出された場合と同様に扱われます。 したがって、ビューを使用するユーザには、ビュー内で使用される全ての関数を呼び出す権限が必要です。
CREATE OR REPLACE VIEW
が既存のビューに対して使用されると、ビューを定義するSELECTルールのみが変更されます。
所有者、権限、SELECT以外のルールなど他のビューの属性はそのまま変更されません。
置き換えるためにはビューの所有者(所有ロールのメンバである場合も含む)でなければなりません。
簡単なビューは自動更新可能になります。
システムは、ビューに対するINSERT
、UPDATE
、DELETE
文を通常のテーブルの場合と同じ方法で使用できるようにします。
以下の条件のすべてを満たす場合に、ビューは自動更新可能になります。
ビューのFROM
リストには正確に1つだけの項目を持たなければならず、それはテーブルまたは他の更新可能ビューでなければなりません。
ビューの定義の最上位レベルにおいてWITH
、DISTINCT
、GROUP BY
、HAVING
、LIMIT
、OFFSET
を含めてはなりません。
ビューの定義の最上位レベルにおいて集合操作(UNION
、INTERSECT
、EXCEPT
)を含めてはなりません。
ビューの選択リストに、集約関数、ウィンドウ関数、集合を返す関数を含めてはなりません。
自動更新可能ビューでは、更新可能な列と更新不可能な列を混在させることができます。
基になるリレーションの更新可能な列を単純に参照する列は更新可能です。
そうでなければ列は更新不可能で、INSERT
あるいはUPDATE
文でその列に値を設定しようとしたらエラーが発生します。
ビューが自動更新可能であれば、システムはビューに対するINSERT
、UPDATE
またはDELETE
文を基となるベースリレーションへの対応する文に変換します。
ON CONFLICT UPDATE
句を持つINSERT
文は完全にサポートされます。
自動更新可能ビューがWHERE
条件を持つ場合、
ベースリレーションのどの行をビューに対するUPDATE
、DELETE
文により変更可能かをその条件が制限します。
しかしUPDATE
による行の変更の結果WHERE
を満たさなくなり、その結果、ビューからは参照することができなくなることがあります。
同様にINSERT
コマンドはWHERE
条件を満たさず、そのためビューを通して参照することができない行をベースリレーションに挿入する可能性があります(ON CONFLICT UPDATE
はビューを通して見えない既存の行に同様に影響を及ぼすかもしれません)。
CHECK OPTION
はINSERT
やUPDATE
がビューで見ることができない行を作るのを防ぐために使うことができます。
自動更新可能ビューがsecurity_barrier
属性を持つ場合、ビューのすべてのWHERE
条件(およびLEAKPROOF
の演算子を使ったすべての条件)が、必ず、ビューのユーザが追加した条件より前に評価されます。
詳細は39.5. ルールと権限を参照してください。
このため、最終的には(ユーザのWHERE
条件を満たさないために)戻されない行もロックされてしまう場合があることに注意してください。
EXPLAIN
を使って、リレーションのレベルでどの条件が使われ(その結果、行をロックしない)、どの条件が使われないかを調べることができます。
これらの条件をすべて満たさないより複雑なビューはデフォルトで読み取り専用です。
システムはビューに対する挿入、更新、削除を許可しません。
ビューに対するINSTEAD OF
トリガを作成することで、更新可能ビューの効果を得ることができます。
このトリガはビューに対する挿入試行などを他のテーブルに対する適切な操作に変換するものでなければなりません。
詳細についてはCREATE TRIGGERを参照してください。
他にもルールを作成する(CREATE RULE参照)ことでも実現できますが、実際にはトリガの方が理解しやすく正しく使用するのが容易です。
ビューに対する挿入、更新、削除を行うユーザは、ビューに対して対応する挿入、更新、削除権限を持たなければなりません。 さらにビューの所有者は基となるベースリレーションに対する適切な権限を持たなければなりません。 しかし、更新を行うユーザは基となるベースリレーションに対する権限をまったく必要としません(39.5. ルールと権限参照)。
全てのコメディ映画(Comedy films)からなるビューを作成します。
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy';
これはビューを作成した時点でfilm
テーブル内にある列を持つビューを作成します。
ビューを作成するために*
が使用されていますが、その後にテーブルに追加された列はビューには含まれません。
LOCAL CHECK OPTION
を使ってビューを作成します。
CREATE VIEW universal_comedies AS SELECT * FROM comedies WHERE classification = 'U' WITH LOCAL CHECK OPTION;
これはcomedies
ビューに基づくビューを作成し、kind = 'Comedy'
かつclassification = 'U'
である映画だけを表示します。
このビューでの行のINSERT
やUPDATE
は、classification = 'U'
でなければ拒絶されますが、映画のkind
は検査されません。
CASCADED CHECK OPTION
でビューを作成します。
CREATE VIEW pg_comedies AS SELECT * FROM comedies WHERE classification = 'PG' WITH CASCADED CHECK OPTION;
これは新しい行についてkind
とclassification
の両方を検査するビューを作成します。
更新可能な列と更新不可能な列が混在するビューを作成します。
CREATE VIEW comedies AS SELECT f.*, country_code_to_name(f.country_code) AS country, (SELECT avg(r.rating) FROM user_ratings r WHERE r.film_id = f.id) AS avg_rating FROM films f WHERE f.kind = 'Comedy';
このビューはINSERT
、UPDATE
、DELETE
をサポートします。
films
テーブルからのすべての列は更新可能ですが、計算される列country
とavg_rating
は更新できません。
1から100までの数からなる再帰的ビューを作成します。
CREATE RECURSIVE VIEW public.nums_1_100 (n) AS VALUES (1) UNION ALL SELECT n+1 FROM nums_1_100 WHERE n < 100;
上記のCREATE
において再帰的ビューの名前はスキーマ修飾されていますが、その内側の自己参照はスキーマ修飾されていないことに注意してください。
これは、暗黙的に作成されるCTEの名前はスキーマ修飾できないからです。
CREATE OR REPLACE VIEW
はPostgreSQLの言語拡張です。
一時ビューという概念も言語拡張です。
同様にWITH ( ... )
句も拡張です。