CREATE VIEW

CREATE VIEW — 定義一個新的檢視表

語法

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

說明

CREATE VIEW 定義某個查詢的檢視表。此檢視表並不會實體上存在。相反地,每次在查詢中引用檢視表時才進行查詢。

CREATE OR REPLACE VIEW 類似,只是如果已經存在同名的檢視表,則替換它。新的查詢必須産生與現有檢視表查詢所産生相同的欄位(即,相同順序且具有相同資料型別的相同欄位名稱),但它可以會在列表末尾增加其他欄位。產生輸出欄位的計算可能完全不同。

如果加上了綱要名稱(例如,CREATE VIEW myschema.myview ...),則會在指定的綱要中建立檢視表。否則,它將在目前綱要中建立。臨時檢視表存在於特殊綱要中,因此在建立臨時檢視圖時不能加上綱要名稱。檢視表的名稱必須與同一綱要中的任何其他檢視表、資料表、序列、索引或外部資料表的名稱不同。

參數

TEMPORARY or 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 (string)

此參數可能是 local 或 cascaded,等同於指定 WITH [CASCADED | LOCAL] CHECK OPTION(見下文)。可以使用 ALTER VIEW 在現有檢視表上變更此選項。

security_barrier (boolean)

security_invoker (boolean)

此選項會根據檢視表使用者而不是檢視表所有者的權限檢查底層基本關連。相關細節,請參閱下面的說明。

query

WITH [ CASCADED | LOCAL ] CHECK 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 可用於檢查自動更新檢視表上的條件。但是具有 INSTEAD OF 觸發器的基本檢視表上的條件將不會檢查(CASCADED 選項不會延伸影響到觸發器可更新檢視表,並且將忽略直接在觸發器可更新檢視表上定義的任何檢查選項)。如果檢視表或其任何基本關連具有導致 INSERT 或 UPDATE 指令被重寫的 INSTEAD 規則,則在重寫的查詢中將忽略所有檢查選項,包括在與關連之上定義的自動可更新檢視表的任何檢查與 INSTEAD 規則。

注意

請注意,檢視表欄位的名稱和型別會按您希望的方式分配。例如:

CREATE VIEW vista AS SELECT 'Hello World';

是不好的形式,因為欄位名稱預設為 ?column?;此外,欄位資料型別預設為 text,可能不是您想要的。在檢視表的結果中,字串的更好形式是這樣的:

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

如果檢視表的 security_invoker 屬性設定為 true,則對底層基本關連的存取權限由執行查詢的使用者而非檢視表所有者的權限決定。因此,安全的檢視表的使用者必須對該檢視表及其底層基本關連具有相關權限。

If any of the underlying base relations is a security invoker view, it will be treated as if it had been accessed directly from the original query. Thus, a security invoker view will always check its underlying base relations using the permissions of the current user, even if it is accessed from a view without the security_invoker property.

Functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore, the user of a view must have permissions to call all functions used by the view. Functions in the view are executed with the privileges of the user executing the query or the function owner, depending on whether the functions are defined as SECURITY INVOKER or SECURITY DEFINER. Thus, for example, calling CURRENT_USER directly in a view will always return the invoking user, not the view owner. This is not affected by the view's security_invoker setting, and so a view with security_invoker set to false is not equivalent to a SECURITY DEFINER function and those concepts should not be confused.

The user creating or replacing a view must have USAGE privileges on any schemas referred to in the view query, in order to look up the referenced objects in those schemas. Note, however, that this lookup only happens when the view is created or replaced. Therefore, the user of the view only requires the USAGE privilege on the schema containing the view, not on the schemas referred to in the view query, even for a security invoker view.

在現有檢視表上使用 CREATE OR REPLACE VIEW 時,僅更改檢視表定義的 SELECT 規則。其他檢視表屬性(包括所有權,權限和非 SELECT 規則)保持不變。您必須擁有檢視表才能替換它(這包括成為擁有角色的成員)。

可更新的檢視表(Updatable Views)

簡單檢視表可自動更新:系統將允許 INSERT,UPDATE 和 DELETE 語句以與一般資料表相同的方式在檢視表上使用。如果檢視表滿足以下所有條件,則檢視表可自動更新:

  • 檢視表必須在其 FROM 列表中只有一個項目,該列表必須是資料表或另一個可更新檢視表。

  • 檢視表定義不得在最上層有 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 指令建立檢視表不可見的資料列。

範例

建立一個包含所有喜劇電影的檢視表:

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;

這將建立一個基於喜劇檢視表的檢視表,僅顯示具有 kind = 'Comedy' 和 classification='U' 的電影。如果新的資料列沒有 classification = 'U',則將拒絕任何在檢視表中插入或更新資料列的嘗試,但不會檢查 film 中的 kind 欄位。

使用 CASCADED CHECK OPTION 建立檢視表:

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG'
    WITH CASCADED CHECK OPTION;

這將建立一個檢視表,檢查新資料列的 classification 和 kind。

混合可更新和不可更新欄位建立檢視表:

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。film 資料表中的所有欄位都是可更新的,而計算欄位 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(...)子句也是一個延伸功能。

參閱

Was this helpful?