Postgresql 中文操作指南

CREATE VIEW

CREATE VIEW - 定义新视图

Synopsis

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

Description

CREATE VIEW 定义了查询的视图。此视图不会实际具体化。相反，每次在查询中引用此视图时，该查询才会运行。

CREATE OR REPLACE VIEW 相似，但如果已存在同名视图，则它会被替换。新查询必须生成现有视图查询生成的所有列（也就是说，具有相同顺序的相同列名，且具有相同数据类型），但它会将额外的列添加到列表的末尾。生成输出列的计算可以完全不同。

如果给出了架构名称（例如 CREATE VIEW myschema.myview …​ ），则该视图将在指定的架构中创建。否则，它将在当前架构中创建。临时视图存在于专用架构中，因此在创建临时视图时不能给出架构名称。视图的名称必须区别于同一架构中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称。

Parameters

  • TEMPORARY or TEMP

    • 如果指定，该视图将创建为临时视图。临时视图在当前会话结束时自动删除。名称相同的现有永久关系在临时视图存在期间对当前会话不可见，除非它们引用架构限定名称。

    • 如果视图引用的任何表都为临时表，则该视图将创建为临时视图（无论是否指定了 TEMPORARY ）。

  • RECURSIVE

    • 创建一个递归视图。语法为：

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

  • is equivalent to

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 ] [, …​ ] )_

  • 此子句指定视图的可选参数；支持以下参数：

  • 上述所有选项都可以使用 ALTER VIEW 在现有视图上进行更改。

    • query

  • SELECT 或 VALUES 命令，它将提供视图的列和行。

    • WITH [ CASCADED | LOCAL ] CHECK OPTION

  • 此选项控制自动可更新视图的行为。当指定此选项后，系统将检查视图上的 INSERT 和 UPDATE 命令，以确保新行满足视图定义的条件（也就是说，检查新行以确保通过视图可见）。如果它们不可见，则将拒绝更新。如果没有指定 CHECK OPTION ，则允许在视图上使用 INSERT 和 UPDATE 命令创建通过视图不可见的行。支持以下检查选项：

  • 不能将 CHECK OPTION 与 RECURSIVE 视图结合使用。

  • 请注意， CHECK OPTION 仅支持自动可更新的视图，且没有 INSTEAD OF 触发器或 INSTEAD 规则。如果基于具有 INSTEAD OF 触发器的基本视图定义了自动可更新视图，则可以将 LOCAL CHECK OPTION 用于检查自动可更新视图上的条件，但不会检查具有 INSTEAD OF 触发器的基本视图上的条件（级联检查选项不会级联到可由触发器更新的视图，并且直接在可由触发器更新的视图上定义的任何检查选项都将被忽略）。如果视图或其任何基本关系具有 INSTEAD 规则，导致 INSERT 或 UPDATE 命令被重写，那么将在重写的查询中忽略所有检查选项，包括通过 INSTEAD 规则在关系上定义的自动可更新视图的任何检查。

    • check_option (enum)

  • 此参数可以是 local 或 cascaded ，并且等效于指定 WITH [ CASCADED | LOCAL ] CHECK OPTION （见下文）。

    • security_barrier (boolean)

  • 如果视图旨在提供行级安全性，则应使用此参数。有关完整详细信息，请参见 Section 41.5 。

    • security_invoker (boolean)

  • 此选项将导致根据视图用户的权限检查底层基本关系，而不是视图所有者的权限。有关完整详细信息，请参见以下注释。

    • LOCAL

  • 仅根据视图本身中直接定义的条件检查新行。不检查在基础基本视图上定义的任何条件（除非它们也指定 CHECK OPTION ）。

    • CASCADED

  • 根据视图和所有基础基本视图的条件检查新行。如果指定了 CHECK OPTION ，但没有指定 LOCAL 或 CASCADED ，则假定 CASCADED 。

Notes

使用 DROP VIEW 语句删除视图。

请注意，视图列的名称和类型将按照您希望的方式分配。例如：

CREATE VIEW vista AS SELECT 'Hello World';

是不好的格式，因为列名默认为 ?column? ；此外，列数据类型默认为 text ，这可能不是您想要的。视图结果中字符串文字的更好样式类似于：

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

默认情况下，对视图中引用的基础基本关系的访问由视图所有者的权限决定。在某些情况下，可用于对基础表提供安全但受限的访问。但是，并非所有视图都是防篡改的；有关详细信息，请参见 Section 41.5 。

如果视图的 security_invoker 属性设置为 true ，则对基础基本关系的访问由执行查询的用户权限（而不是视图所有者）决定。因此，安全调用者视图的用户必须对视图及其基础基本关系具有相关权限。

如果任何底层基本关系是安全调用方视图，则它将被视为直接从原始查询中访问的一样。因此，安全调用者视图将始终使用当前用户的权限检查其基础基本关系，即使是从没有 security_invoker 属性的视图访问的也是如此。

如果任何基础基本关系启用了 row-level security ，那么默认情况下，将应用视图所有者的行级安全策略，并且由视图所有者的权限来决定对那些策略引用的任何其他关系的访问。但是，如果将视图的 security_invoker 设置为 true ，则将代替使用调用用户的策略和权限，就像基础关系已使用视图直接从查询中引用一样。

在视图中调用的函数的处理方式与直接使用视图从查询中调用的函数的处理方式相同。因此，视图的用户必须有权调用视图使用的所有函数。根据函数定义为 SECURITY INVOKER 还是 SECURITY DEFINER ，视图中的函数以执行查询的用户权限或函数所有者的权限执行。因此，例如，在视图中直接调用 CURRENT_USER 将总是返回调用用户，而不是视图所有者。这不会受到视图 security_invoker 设置的影响，因此将 security_invoker 设置为 false 的视图与 SECURITY DEFINER 函数 not 等效，并且不应混淆这些概念。

创建或替换视图的用户必须对视图查询中引用的任何模式具有 USAGE 权限，以便在这些模式中查找引用的对象。但是，请注意，此查找仅在创建或替换视图时发生。因此，即使对于安全调用者视图，视图的用户也只需要对包含视图的模式具有 USAGE 权限，而无需对视图查询中引用模式的权限。

在现有视图上使用 CREATE OR REPLACE VIEW 时，只会更改视图的定义 SELECT 规则，以及任何 WITH ( …​ ) 参数及其 CHECK OPTION 。其他视图属性（包括所有权、权限和非 SELECT 规则）保持不变。您必须拥有此视图才能替换它（这包括成为拥有角色的成员）。

Updatable Views

简单视图可以自动更新：系统将允许在视图上使用 INSERT 、 UPDATE 和 DELETE 语句，就像在普通表上使用这些语句一样。如果视图满足以下所有条件，则它可以自动更新：

可以自动更新的视图可能包含可更新和不可更新的列的混合。如果某列是基础基本关系的可更新列的简单引用，则该列可更新；否则，该列是只读的，并且如果 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 的运算符的任何条件）将始终在用户添加的任何条件之前进行评估。有关完整详细信息，请参见 Section 41.5 。请注意，因此，最终没有返回的行（因为它们未通过用户的 WHERE 条件）可能仍会最终被锁定。 EXPLAIN 可用于查看在关系级别应用的条件（因此不会锁定行）以及未应用的条件。

不满足所有这些条件的更复杂视图默认情况下是只读的：系统不允许在视图上插入、更新或删除。您可以通过在视图上创建 INSTEAD OF 触发器来获得可更新视图的效果，该触发器必须将视图上尝试的插入等转换为对其他表的适当操作。更多信息，请参见 CREATE TRIGGER 。另一种可能性是创建规则（参见 CREATE RULE ），但实际上触发器更容易理解和正确使用。

请注意，在视图上执行插入、更新或删除的用户必须对该视图具有相应的插入、更新或删除权限。此外，默认情况下，视图的所有者必须对基础基本关系具有相关权限，而执行更新的用户则不需要对基础基本关系具有任何权限（请参见 Section 41.5 ）。但是，如果将视图的 security_invoker 设置为 true ，则执行更新的用户（而不是视图所有者）必须对基础基本关系具有相关权限。

Examples

创建包含所有喜剧电影的视图：

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 public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;

请注意，尽管该 CREATE 中的递归视图名称是经过架构限定的，但其内部自引用未经架构限定。这是因为隐式创建的 CTE 的名称不能经过架构限定。

Compatibility

CREATE OR REPLACE VIEW 是一个 PostgreSQL 语言扩展。临时视图的概念也是如此。 WITH ( …​ ) 子句也是一个扩展，安全障碍视图和安全调用者视图也是如此。

See Also

ALTER VIEW ， DROP VIEW ， CREATE MATERIALIZED VIEW