使用CREATE VIEW定义查询视图_云原生数据库 PolarDB(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的名称不能是模式限定的。