CREATE VIEW語句文法與使用-雲原生資料庫 PolarDB-阿里雲

CREATE VIEW用於定義一個查詢的視圖。

簡介

CREATE VIEW定義一個查詢的視圖。該視圖不會被物化，即它不會在資料庫中儲存資料的實際副本。每當視圖被引用執行查詢時，資料庫都會根據定義的查詢語句動態地產生結果。

CREATE OR REPLACE VIEW命令用於建立或替換一個已存在的同名視圖。在PolarDB中，預設情況下，您可以在新視圖定義中自由地更改列的順序、添加或刪除列。如果您希望該命令的行為與PostgreSQL社區的標準保持一致，您可以禁用polar_enable_or_replace_view_alter_column參數。禁用該參數後，新定義的查詢必鬚生成與現有視圖完全相同的列，包括相同的順序、列名和資料類型。不過，您還可以在列的列表末尾添加新的列，而且即使列的來源或計算邏輯發生變化，也是允許的。

建立視圖時，如果指定了模式名（例如，CREATE VIEW myschema.myview ...），則視圖將在該指定模式下建立。如果未指定模式名，則預設在當前模式中建立視圖。由於臨時視圖被建立在一個特殊的模式裡，建立臨時視圖時無法指定模式名。視圖的名稱在同一模式下必須是唯一的，不得與其他視圖、表、序列、索引或外部表格的名稱相同。

文法

   CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

參數

TEMPORARY/TEMP
如果指定為臨時視圖，系統將在當前會話結束時自動刪除該視圖。同時，在臨時視圖存在的情況下，任何已有同名的非臨時視圖將對當前會話隱藏，除非顯式地通過模式限定的名稱來引用這些非臨時視圖。
如果視圖引用了至少一個暫存資料表，則該視圖也會自動被建立為臨時視圖，即使沒有顯式指定TEMPORARY關鍵字。

RECURSIVE

建立一個遞迴視圖。文法如下：

CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;

等效於以下文法：

CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;

說明

對於一個遞迴視圖，必須指定一個視圖列名列表。

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)
  需要使視圖支援行級安全性特性，請使用該參數。
query
定義視圖的行和列結構，需要使用一個SELECT或VALUES語句。
WITH [ CASCADED | LOCAL ] CHECK OPTIONCHECK OPTION
控制視圖是否應確保INSERT和UPDATE操作產生的新行滿足視圖定義的條件，即新行必須對視圖可見。如果新行違反了這些條件，操作將被拒絕。未指定CHECK OPTION時，對視圖的INSERT和UPDATE操作可能會產生視圖本身不可見的行。支援以下檢查選項：
- LOCAL：新行將僅根據定義在該視圖上的直接條件進行檢查。底層視圖上定義的任何條件不會受到檢查，除非那些視圖同樣指定了CHECK OPTION。
- CASCADED：新行會根據該視圖以及任何底層視圖的條件進行檢查。如果指定了CHECK OPTION而沒有明確選擇LOCAL或CASCADED，系統將預設採用CASCADED檢查。值得注意的是，不推薦將CHECK OPTION與RECURSIVE視圖結合使用。
- CHECK OPTION：僅在以下情況下受支援：視圖必須是可更新的，並且不能有INSTEAD OF觸發器或INSTEAD規則。如果一個可更新視圖建立在一個有INSTEAD OF觸發器的基視圖之上，LOCAL CHECK OPTION可以用於檢查該可更新視圖上的條件。但是，基視圖上的任何條件不會受到LOCAL CHECK OPTION的檢查。此外，CASCADED CHECK OPTION不會傳遞到那些由觸發器可更新的視圖，直接定義在這些視圖上的任何檢查選項都會被忽略。
  如果視圖或其任何底層關係有INSTEAD規則，這些規則可能會重寫INSERT或UPDATE命令。在這種情況下，被重寫的查詢將忽略所有檢查選項，無論它們是直接定義在視圖上還是在擁有INSTEAD規則的底層關係上。

說明

確保在建立視圖時，列的名稱和類型都按照預期進行了指定。樣本如下：

CREATE VIEW vista AS SELECT 'Hello World';

使用預設的列名?column?和資料類型text建立視圖通常是不推薦的，因為這可能不符合使用者的意圖。建立視圖時，應當指定明確的列名和資料類型以避免這種情況。一個更好的做法是給視圖結果中的每個字串指定一個具體的名稱，樣本如下：

CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

對於在視圖中引用的表，存取權限取決於視圖的擁有者所具有的許可權。這種機制在某些情況下可以用來安全地提供對底層表的受限訪問。然而，並不是所有的視圖都是對資料篡改安全的。視圖中使用的函數許可權同樣重要，它們會被視為是從調用視圖的查詢中直接調用的。因此，使用視圖的使用者必須擁有執行視圖中所有引用函數的許可權。

使用CREATE OR REPLACE VIEW修改現有視圖時，只會更新視圖的定義（即SELECT語句）。視圖的其他屬性，例如所有權、許可權和與非SELECT操作相關的規則，都將保持不變。要替換一個視圖，你必須是其擁有者或者是擁有者角色的成員。

可更新視圖

簡單視圖支援自動更新，意味著系統允許使用者對這些視圖使用INSERT、UPDATE以及DELETE語句，就像對常規表進行操作一樣。視圖必須滿足以下條件才能被視為可更新：

視圖的FROM子句中必須僅包含一項，該項可以是一個表或另一個可更新的視圖。
視圖定義的頂層SELECT語句不能包括WITH子句、DISTINCT關鍵字、GROUP BY子句、HAVING子句、LIMIT子句或OFFSET子句。
視圖定義的頂級SELECT語句不能包含集合操作符。例如，UNION、INTERSECT或EXCEPT。
視圖的挑選清單中不能包含彙總函式、視窗函數或返回集合的函數。

在可更新的視圖中，可更新列和不可更新列可以共存。如果一個視圖列是對底層基礎資料表中可更新列的直接引用，那麼該列也是可更新的。如果不是這種情況，該列將是唯讀，嘗試在INSERT或UPDATE語句中對其賦值會觸發錯誤。

一旦確定視圖是可更新的，系統會自動將對視圖執行的任何INSERT、UPDATE或者DELETE操作轉換為對底層基礎資料表的相應操作。此外，帶有ON CONFLICT UPDATE子句的INSERT語句也已完全支援。

在可更新的視圖中，如果定義了WHERE子句，它將定義哪些底層表的行可以被視圖上的UPDATE和DELETE操作所影響。然而，通過UPDATE操作修改的行可能會導致這些行不再符合視圖的WHERE條件，從而使得它們在視圖中不再可見。同樣，INSERT操作可能會添加不滿足視圖WHERE條件的新行到底層表中，這些行同樣在視圖中不可見（ON CONFLICT UPDATE也可能同樣影響現有的、在視圖中不可見的行）。

為了避免INSERT和UPDATE操作造成這種情況，即在視圖中不可見的行被添加或修改，可以使用CHECK OPTION。它的作用是確保所有通過視圖插入或更新的行在執行操作後仍然滿足視圖的WHERE條件，因而能夠在視圖中可見。

當一個可更新視圖設定了security_barrier屬性，視圖中的WHERE條件（以及任何包含LEAKPROOF操作符的條件）會在視圖使用者提供的任何其他條件之前執行。這意味著，在使用者的WHERE條件過濾之前，這些安全屏障條件就已經生效了。

說明

即使一些行最終不會被返回，因為它們未通過使用者指定的WHERE條件，它們仍然可能會被鎖定，因為安全屏障條件已經提前計算過了。
要瞭解哪些條件是否是在底層關係上應用的（從而可能鎖定行），可以使用EXPLAIN語句進行查詢分析。

複雜視圖，即那些不符合可更新條件的視圖，預設被視為唯讀，系統不會允許在這些視圖上進行插入、更新或刪除操作。要使這種視圖可更新，可以在視圖上建立一個INSTEAD OF觸發器。該觸發器負責將嘗試對視圖進行的插入、更新或刪除操作轉換為對底層表進行的相應操作。有關建立觸發器的詳細資料，請參考CREATE TRIGGER。也可以通過定義規則（參見CREATE RULE）來實現，儘管在實踐中，觸發器通常更易於理解和使用。

執行對視圖的插入、更新或刪除操作的使用者必須具備相應的許可權：在視圖上的插入許可權、更新許可權或刪除許可權。除此之外，視圖的擁有者還需要擁有對底層基礎資料表相應的許可權。

說明

執行資料修改操作的使用者不需要直接在底層基礎資料表上擁有任何許可權。

樣本

建立一個由所有喜劇電影組成的視圖。
```
    CREATE VIEW comedies AS
        SELECT *
        FROM films
        WHERE kind = 'Comedy';
```
建立的視圖包含建立時film表中的列。儘管*被用來建立該視圖，建立視圖之後被加入到該表中的列不會成為該視圖的組成部分。

建立一個由所有電影名字組成的視圖。

CREATE VIEW film_names AS
    SELECT title
    FROM films;

預設情況下，在PolarDB中，可以使用以下任一文法在film_names視圖中添加release欄位。
```
文法一：
CREATE OR REPLACE VIEW film_names AS
    SELECT title, release
    FROM films;

文法二：
CREATE OR REPLACE VIEW film_names AS
    SELECT release, title
    FROM films;
```
說明
如果polar_enable_or_replace_view_alter_column參數關閉，則只能使用文法一。
建立帶有LOCAL CHECK OPTION的視圖。
```
CREATE VIEW universal_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'U'
    WITH LOCAL CHECK OPTION;
```
這將建立一個基於comedies視圖的視圖，只顯示kind = 'Comedy'和classification = 'U'的電影。如果新行沒有classification = 'U'，在該視圖中的任何INSERT或UPDATE嘗試將被拒絕，但是電影的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;
```
說明
上述SQL語句中，儘管遞迴的視圖名稱是模式限定的，但它內部的自引用不是模式限定的。這是因為隱式建立的CTE的名稱不能是模式限定的。