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 name (columns) AS SELECT ...;
等效于
CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM 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命令 创建通过该视图不可见的行。支持下列检查选项:
- LOCAL
-
只根据直接定义在该视图本身的条件检查新行。任何定义在底层基视图上的 条件都不会被检查(除非它们也指定了CHECK OPTION)。
- CASCADED
-
会根据该视图和所有底层基视图上的条件检查新行。如果 CHECK OPTION被指定,并且没有指定 LOCAL和CASCADED,则会假定为 CASCADED。
CHECK OPTION不应该和RECURSIVE视图一起使用。
注意,只有在自动可更新的、没有INSTEAD OF触发器或者 INSTEAD规则的视图上才支持CHECK OPTION。 如果一个自动可更新的视图被定义在一个具有INSTEAD OF 触发器的基视图之上,那么LOCAL CHECK OPTION可以被 用来检查该自动可更新的视图之上的条件,但具有INSTEAD OF 触发器的基视图上的条件不会被检查(一个级联检查选项将不会级联到一个 触发器可更新的视图,并且任何直接定义在一个触发器可更新视图上的检查 选项将被忽略)。如果该视图或者任何基础关系具有导致 INSERT或UPDATE命令被重写的 INSTEAD规则,那么在被重写的查询中将忽略所有检查选项, 包括任何来自于定义在带有INSTEAD规则的关系之上的自动 可更新视图的检查。
注解
使用DROP VIEW语句删除视图。
要小心视图列的名称和类型将会按照你想要的方式指定。例如:
CREATE VIEW vista AS SELECT 'Hello World';
从两个方面来说是一种不好的形式:列名默认为?column?,并且 列数据类型默认为unknown。如果你在视图结果中想要的是一个 字符串,使用下面的形式:
CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
对视图中引用的表的访问由视图拥有者的权限决定。在某些情况下,这可以 被用来提供安全但是受限的底层表访问。不过,并非所有视图都对篡改是安 全的,详见第 39.5 节。在视图中调用的函数会被 同样对待,就好像是直接在使用该视图的查询中调用它们一样。因此,一个 视图的用户必须具有调用视图所使用的全部函数的权限。
当CREATE OR REPLACE VIEW被用在一个现有视图上时, 只有该视图的定义 SELECT 规则被改变。其他包括拥有关系、权限和非 SELECT 规则在内的视图属性不会被更改。要替换视图,你必须拥有它(包括 作为拥有角色的一个成员)。
可更新视图
简单视图是自动可更新的:系统将允许在这类视图上以在常规表上相同的方式 使用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命令创建这类从视图中无法看到的行。
如果一个自动可更新视图被标记了security_barrier属性,那么 所有该属性的WHERE条件(以及任何使用标记为 LEAKPROOF的操作符的条件)将在该视图使用者的任何条件 之前计算。详见第 39.5 节。注意正因为这样,不会 被最终返回的行(因为它们不会通过用户的WHERE条件)可能 仍会结束被锁定的状态。可以用EXPLAIN来查看 哪些条件被应用在关系层面(并且因此不锁定行)以及哪些不会被应用在关系 层面。
一个更加复杂的不满足所有这些条件的视图默认是只读的:系统将不允许在 该视图上的插入、更新或者删除。可以通过在该视图上创建一个 INSTEAD OF触发器来获得可更新视图的效果,该触发器必须 把该视图上的尝试的插入等转换成其他表上合适的动作。更多信息请见CREATE TRIGGER。另一种可能性是创建规则(见 CREATE RULE),不过实际中触发器更容易理解和正确使用。
注意在视图上执行插入、更新或删除的用户必须具有该视图上相应的插入、 更新或删除特权。此外,视图的拥有者必须拥有底层基本关系上的相关特权, 但是执行更新的用户并不需要底层基本关系上的任何权限(见 第 39.5 节)。
示例
创建一个由所有喜剧电影组成的视图:
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'的电影。 如果新行没有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 nums_1_100 (n) AS VALUES (1) UNION ALL SELECT n+1 FROM nums_1_100 WHERE n < 100;