このトピックでは、クエリのビューを定義するために使用されるCREATE VIEWについて説明します。
概要
CREATE VIEWは、クエリのビューを定義します。 作成されたビューは、データレプリカをデータベースに保存しません。 ビューがクエリによって参照されると、データベースは、定義したクエリ文に基づいて結果を動的に生成します。
CREATE OR REPLACE VIEWは、新しいビューを作成するか、同じ名前を持つ既存のビューを置き換えます。 デフォルトでは、PolarDBを使用すると、列の順序を変更したり、新しいビュー定義で列を追加または削除したりできます。 このコマンドをPostgreSQLコミュニティエディションのコマンドと同じように動作させるには、polar_enable_or_replace_view_alter_列パラメーターを無効にします。 このパラメーターを無効にした後、列の順序、列名、データ型など、既存のビューと同じ列を生成する新しいクエリを定義する必要があります。 ただし、列リストの最後に新しい列を追加することはできます。 新しい列のデータソースとコンピューティングロジックは、既存の列とは異なる場合があります。
ステートメントでスキーマ名を指定すると、指定したスキーマにビューが作成されます。 たとえば、CREATE VIEW myschema.myview...文では、myviewビューがmyschemaスキーマに作成されます。 それ以外の場合、ビューは現在のスキーマに作成されます。 一時ビューは特別なスキーマで作成されるため、一時ビューの作成時にスキーマ名を指定することはできません。 ビューの名前はスキーマ内で一意である必要があり、別のビュー、テーブル、シーケンス、インデックス、または外部テーブルの名前と同じであってはなりません。
構文
CREATE [ OR REPLACE ] [ TEMP | 一時] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]]
[WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[[CASCADED | ローカル] チェックオプション付き] Parameters
一時/TEMP一時ビューを作成します。 一時ビューは、現在のセッションの最後に自動的に削除されます。 一時ビューが存在する場合、一時ビューと同じ名前を持つ既存の非一時ビューは、現在のセッションから非表示になります。 スキーマ修飾名を使用して、このようなビューを明示的に参照できます。
ビューが少なくとも1つの一時テーブルを参照する場合、
temporaryパラメーターが明示的に指定されていなくても、ビューは一時ビューとして自動的に作成されます。RECURSIVE再帰ビューを作成します。 次の構文が使用されます。
CREATE RECURSIVE VIEW [スキーマ] 。 ] view_name (column_names) AS SELECT ...;作成されたビューは、次の構文を使用して作成されたビューと同じです。
CREATE VIEW [スキーマ] 。 ] view_name AS RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_namesからview_name;説明再帰ビューの列名のリストを指定する必要があります。
名前作成したビューの名前を指定します。 スキーマを指定するかどうかを選択できます。
column_name作成したビューの列名のリストを指定します。 このパラメーターはオプションです。 このパラメーターを指定しない場合、列名はクエリ結果から自動的に推測されます。
WITH (_view_option_name_[=_view_option_value_] [, ... ] )ビューのオプションパラメーターのセットを指定します。 以下のパラメーターがサポートされます。
check_option(enum)このパラメーターは、
localまたはcascadedに設定できます。 このパラメーターの動作は、WITH [ CASCADED | LOCAL ] CHECK OPTIONステートメントと同じです。ALTER VIEWコマンドを使用して、既存のビューのこのパラメーターを変更できます。security_barrier(boolean)ビューのRLS機能を有効または無効にします。
クエリビューの行と列の構造を定義します。
SELECTまたはVALUESステートメントが使用されます。WITH [ CASCADED | ローカル] チェックオプションチェックオプションINSERTおよびUPDATEステートメントによって生成される新しい行が、ビューで定義された条件を満たす必要があるかどうかを指定します。 新しい行が条件を満たす場合、ビューに表示されます。 新しい行が条件を満たさない場合、挿入または更新操作は拒否されます。CHECK OPTIONを指定しない場合、INSERTおよびUPDATEステートメントによって生成された新しい行がビューに表示されない場合があります。 次のチェックオプションがサポートされています。LOCAL: 現在のビューで定義されている条件のみに基づいて、新しい行がチェックされます。 基になるビューで定義された条件は、CHECK OPTIONも指定しない限り、チェックされません。CASCADED: 現在のビューと基になるビューで定義されている条件に基づいて、新しい行がチェックされます。CHECK OPTIONを指定し、LOCALまたはCASCADEDを指定しない場合、デフォルトでCASCADEDが使用されます。RECURSIVEビューにはCHECK OPTIONを使用しないことを推奨します。CHECK OPTION:INSTEAD OFトリガーまたはINSTEADルールを含まない更新可能なビューのみがこのオプションをサポートしています。INSTEAD OFトリガーを含む基本ビューに基づいて更新可能ビューが作成されている場合、LOCAL CHECK OPTIONを指定して、更新可能ビューで定義された条件に基づいて新しい行をチェックできます。 ただし、基本ビューで定義されている条件は無視されます。 さらに、CASCADED CHECK OPTIONは、トリガーによって更新可能なビューにはカスケードされません。 このようなビューで直接定義された条件は無視されます。ビューまたはその基になる関係に
INSTEADルールが含まれている場合、INSERTまたはUPDATEステートメントはルールによって変更されます。 この場合、新しいクエリは、ビューで直接定義されている条件と、INSTEADルールを含む基になるリレーションシップで定義されている条件を無視します。
使用上の注意
ビューを作成するときは、必ず列の名前とタイプを指定してください。 サンプルコード：
SELECT 'Hello World 'としてビュービスタを作成します。デフォルトの列名を使用する代わりに、要件に基づいて列名とタイプを指定することを推奨します。? コラム? およびデータ型text 潜在的なリスクを回避するために、わかりやすい列名とデータ型を指定できます。 ベストプラクティスは、ビュー結果の各文字列に特定の名前を付けることです。 次のサンプルコードを参照してください。
CREATE VIEW vista AS SELECTテキスト 'Hello World 'AS hello;ビューで参照されるテーブルの場合、テーブルへのアクセス許可はビュー所有者のアクセス許可によって異なります。 これにより、場合によっては、基礎となるテーブルへの安全で制限されたアクセスが提供されます。 ただし、すべてのビューがデータ改ざんに対して安全であるわけではありません。 また、ビューで使用される関数の権限にも注意を払う必要があります。 このような関数は、ビューのクエリから直接呼び出されます。 したがって、ビューユーザーには、ビューで参照されるすべての関数を実行する権限が必要です。
CREATE OR REPLACE viewを使用して既存のビューを変更すると、ビューの定義 (SELECTステートメント) のみが更新されます。 SELECTステートメントで定義されていない所有権、権限、ルールなど、ビューのその他のプロパティは変更されません。 ビューを置き換えるには、ビューの所有者またはビューの所有者ロールを持つユーザーである必要があります。
更新可能なビュー
シンプルなビューは自動更新をサポートします。 通常のテーブルと同じ方法で、INSERT、UPDATE、およびDELETEステートメントを単純なビューで実行できます。 更新可能なビューは、次の条件を満たす必要があります。
ビューの
FROM句には、テーブルまたは更新可能なビューが1つだけ含まれます。ビュー定義の最上位
SELECTステートメントには、WITH句、DISTINCTキーワード、GROUP BY句、HAVING句、LIMIT句、またはOFFSET句は含まれません。ビュー定義のトップレベルの
SELECTステートメントには、UNION、INTERSECT、EXCEPTなどのset演算子は含まれません。ビュー定義のSELECTステートメントのリストには、集計関数、ウィンドウ関数、またはコレクションを返す関数が含まれていません。
更新可能なビューには、更新可能な列と読み取り専用の列の両方を同時に含めることができます。 ビューの列が基になる基本テーブルの更新可能な列を直接参照する場合、この列は更新可能です。 それ以外の場合、列は読み取り専用です。 INSERTまたはUPDATEステートメントを使用して列に値を代入しようとすると、エラーが報告されます。
更新可能なビューでINSERT、UPDATE、またはDELETEステートメントが実行されると、システムはこれらのステートメントを基になる基本テーブルで自動的に実行します。 さらに、ON CONFLICT UPDATE句を含むINSERTステートメントは完全にサポートされています。
更新可能なビューの定義にWHERE句が含まれている場合、ビューで実行されるUPDATEおよびDELETEステートメントによって、基になるテーブルのどの行を更新できるかを定義します。 ただし、このようなUPDATEステートメントによって更新された行は、WHERE句で定義されている条件を満たさなくなり、ビューで非表示になる可能性があります。 同様に、INSERT文は、WHERE句で定義されている条件を満たさない新しい行を基になるテーブルに追加する場合があります。 このような行もビューでは見えません。 ON COFLICT UPDATEは、ビューで非表示の既存の行にも影響を与える可能性があります。
CHECK OPTIONを使用すると、INSERTまたはUPDATEステートメントでビューに表示されない行が追加または更新されないようにできます。 このパラメーターは、ビューでINSERTまたはUPDATEステートメントを実行することによって挿入または更新される行が、WHERE句で定義された条件を満たすようにします。 その結果、ビューに行が表示されます。
security_barrierプロパティが設定されている更新可能なビューの場合、ビュー定義のWHERE句で定義されている条件とLEAKPROOF演算子を含む条件は、ビューユーザーが提供する他の条件の前にチェックされます。 これにより、ユーザーが提供するWHERE句の条件の前にセキュリティバリア条件が有効になります。
一部の行は、ユーザーが提供する
WHERE句の条件を満たさないため、返されない場合があります。 しかしながら、そのような行は、事前にセキュリティバリア条件に対してチェックされるので、依然としてロックされ得る。基になるリレーションシップで定義され、行をロックする可能性のある条件の詳細については、
EXPLAINステートメントを実行してクエリを分析します。
更新可能なビューとして認定されていないビューは、複雑なビューです。 複雑なビューは、デフォルトでは読み取り専用です。 システムは、これらのビューに対する挿入、更新、または削除操作を許可しません。 複雑なビューを更新可能にするには、ビューにINSTEAD OFトリガーを作成します。 ビューに対して挿入、更新、または削除操作が実行された場合、このトリガーは基になるテーブルに対して対応する操作を実行しようとします。 トリガーの作成の詳細については、 または、同じ目標を達成するためのルールを作成できます。 ただし、トリガーは通常、理解して使用するのが簡単です。
ビューに対して挿入、更新、または削除操作を実行するユーザーは、ビューに対してそのような操作を実行するための対応する権限を持っている必要があります。 さらに、ビューの所有者は、基になる基本テーブルに対する対応する権限を持っている必要があります。
データ変更操作を実行するユーザーは、基になる基本テーブルに対する権限を必要としません。
例
すべてのコメディ映画で構成されるビューを作成します。
ビューコメディを作成する SELECT * 映画から WHERE kind = 'Comedy';作成されたビューには、
filmsテーブルの列が含まれます。 ビューの作成には*が使用されますが、ビューの作成後にフィルムテーブルに追加された列はビューに含まれません。すべてのフィルム名で構成されるビューを作成します。
ビューfilm_namesを作成する SELECTタイトル から映画;デフォルトでは、PolarDBでは、次のいずれかの構文を使用して、
film_namesビューにreleaseフィールドを追加できます。構文1: 作成または置き換えを行うfilm_namesとして表示 SELECTタイトル、リリース 映画から; 構文2: 作成または置き換えを行うfilm_namesとして表示 SELECTリリース、タイトル から映画;説明polar_enable_or_replace_view_alter_パラメーターが無効の場合、構文1のみを使用できます。LOCAL CHECK OPTIONを含むビューを作成します。CREATE VIEW universal_comedies AS SELECT * コメディから WHERE classification = 'U' ローカルチェックオプション;ビューは
コメディビューに基づいて作成されます。 ビューには、種類パラメータがコメディに設定され、分類パラメータがUに設定されている映画のみが表示されます。挿入または更新された行にclassification = 'U'を指定しないINSERTまたはUPDATEステートメントを実行しようとすると、実行は失敗します。 ただし、この処理では種類パラメータはチェックされません。CASCADED CHECK OPTIONを含むビューを作成します。ビューの作成pg_comedies AS SELECT * コメディから WHERE分類='PG' CASCADEDチェックオプション付き;種類および分類パラメータに基づいてビューが作成される。 条件に基づいて新しい行がチェックされます。更新可能な列と読み取り専用の列の両方を含むビューを作成します。
ビューコメディを作成する SELECT f.* 、 country_code_to_name(f.country_code) (SELECT avg(r.rating) user_ratings rから WHERE r.film_id = f.id) AS avg_rating フィルムからf どこf.kind = 'Comedy';作成されたビューは、
INSERT、UPDATE、およびDELETEステートメントをサポートしています。フィルムテーブルを参照するすべての列は更新可能です。 計算された列countryとavg_ratingは読み取り専用です。1から100までの数値で構成される再帰ビューを作成します。CREATE RECURSIVE VIEW public.nums_1_100 (n) AS 値 (1) ユニオンすべて SELECT n + 1 FROM nums_1_100 WHERE n < 100;説明上記のSQL文では、再帰ビューの名前はスキーマ修飾されていますが、内部の自己参照はスキーマ修飾されていません。 これは、暗黙的に作成された
CTEの名前をスキーマ修飾できないためです。