=========== CREATE VIEW =========== .. container:: refentry :name: SQL-CREATEVIEW .. container:: titlepage .. container:: refnamediv .. rubric:: CREATE VIEW :name: create-view CREATE VIEW — 定义一个新视图 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: 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 ] .. container:: refsect1 :name: id-1.9.3.97.5 .. rubric:: 描述 :name: 描述 ``CREATE VIEW``\ 定义一个查询的视图。该视图不会被 物理上物质化。相反，在每一次有查询引用该视图时，视图的查询都会被运行。 ``CREATE OR REPLACE VIEW``\ 与之相似，但是如果 已经存在一个同名视图，该视图会被替换。新查询必须产生和现有试图查询相同 的列（也就是相同的列序、相同的列名、相同的数据类型），但是它可以在列表 的末尾加上额外的列。产生输出列的计算可以完全不同。 如果给定了一个模式名（例如\ ``CREATE VIEW myschema.myview ...``\ ），那么该视图会被创建在指定的模式中。否则，它会 被创建在当前模式中。临时视图存在于一个特殊模式中，因此创建临时视图时不能 给定一个模式名。视图的名称不能与同一模式中任何其他视图、表、序列、索引或 外部表同名。 .. container:: refsect1 :name: id-1.9.3.97.6 .. rubric:: 参数 :name: 参数 .. container:: variablelist ``TEMPORARY``\ 或者\ ``TEMP`` 如果被指定，视图被创建为一个临时视图。在当前会话结束时会自动 删掉临时视图。当临时视图存在时，具有相同名称的已有永久视图对 当前会话不可见，除非用模式限定的名称引用它们。 如果视图引用的任何表是临时的，视图将被创建为临时视图（不管有 没有指定\ ``TEMPORARY``\ ）。 ``RECURSIVE`` 创建一个递归视图。语法 .. code:: synopsis CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...; 等效于 .. code:: synopsis 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``] [, ... ] ) 这个子句为视图指定一些可选的参数，支持下列参数： .. container:: variablelist ``check_option`` (``string``) 这个参数可以是\ ``local``\ 或者\ ``cascaded``\ ，并且它 等效于指定 ``WITH [ CASCADED | LOCAL ] CHECK OPTION``\ （见下文）。 可以使用\ `ALTER VIEW `__\ 在一个现有视图上修改这个选项。 ``security_barrier`` (``boolean``) 如果希望视图提供行级安全性，应该使用这个参数。 ``query`` 提供视图的行和列的一个\ `SELECT `__\ 或者 `VALUES `__\ 命令。 ``WITH [ CASCADED | LOCAL ] CHECK OPTION`` 这个选项控制自动可更新视图的行为。这个选项被指定时，将检查该视图上的 ``INSERT``\ 和\ ``UPDATE``\ 命令以确保新行满足 视图的定义条件（也就是，将检查新行来确保通过视图能看到它们）。如果新行 不满足条件，更新将被拒绝。如果没有指定\ ``CHECK OPTION``\ ， 会允许该视图上的\ ``INSERT``\ 和\ ``UPDATE``\ 命令 创建通过该视图不可见的行。支持下列检查选项： .. container:: variablelist ``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``\ 规则的关系之上的自动 可更新视图的检查。 .. container:: refsect1 :name: id-1.9.3.97.7 .. rubric:: 注解 :name: 注解 使用\ `DROP VIEW `__\ 语句删除视图。 要小心视图列的名称和类型将会按照你想要的方式指定。例如： .. code:: programlisting CREATE VIEW vista AS SELECT 'Hello World'; 是不好的形式，因为列名默认为\ ``?column?``\ ，而且列的数据类型默认为\ ``text``\ ，这可能不是用户想要的。视图结果中一个字符串更好的风格类似于这样： .. code:: programlisting CREATE VIEW vista AS SELECT text 'Hello World' AS hello; 对视图中引用的表的访问由视图拥有者的权限决定。在某些情况下，这可以 被用来提供安全但是受限的底层表访问。不过，并非所有视图都对篡改是安 全的。在视图中调用的函数会被 同样对待，就好像是直接在使用该视图的查询中调用它们一样。因此，一个 视图的用户必须具有调用视图所使用的全部函数的权限。 当\ ``CREATE OR REPLACE VIEW``\ 被用在一个现有视图上时， 只有该视图的定义 SELECT 规则被改变。其他包括拥有关系、权限和非 SELECT 规则在内的视图属性不会被更改。要替换视图，你必须拥有它（包括 作为拥有角色的一个成员）。 .. container:: refsect2 :name: SQL-CREATEVIEW-UPDATABLE-VIEWS .. rubric:: 可更新视图 :name: 可更新视图 简单视图是自动可更新的：系统将允许在这类视图上以在常规表上相同的方式 使用\ ``INSERT``\ 、\ ``UPDATE``\ 以及 ``DELETE``\ 语句。如果一个视图满足以下条件，它就是自动 可更新的： .. container:: itemizedlist - 在该视图的\ ``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``\ 的操作符的条件）将在该视图使用者的任何条件 之前计算。注意正因为这样，不会 被最终返回的行（因为它们不会通过用户的\ ``WHERE``\ 条件）可能 仍会结束被锁定的状态。可以用\ ``EXPLAIN``\ 来查看 哪些条件被应用在关系层面（并且因此不锁定行）以及哪些不会被应用在关系 层面。 一个更加复杂的不满足所有这些条件的视图默认是只读的：系统将不允许在 该视图上的插入、更新或者删除。可以通过在该视图上创建一个 ``INSTEAD OF``\ 触发器来获得可更新视图的效果，该触发器必须 把该视图上的尝试的插入等转换成其他表上合适的动作。更多信息请见\ `CREATE TRIGGER `__\ 。另一种可能性是创建规则（见 `CREATE RULE `__\ ），不过实际中触发器更容易理解和正确使用。 注意在视图上执行插入、更新或删除的用户必须具有该视图上相应的插入、 更新或删除权限。此外，视图的拥有者必须拥有底层基本关系上的相关权限， 但是执行更新的用户并不需要底层基本关系上的任何权限。 .. container:: refsect1 :name: id-1.9.3.97.8 .. rubric:: 示例 :name: 示例 创建一个由所有喜剧电影组成的视图： .. code:: programlisting CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy'; 创建的视图包含创建时\ ``film``\ 表中的列。尽管\ ```` 被用来创建该视图，后来被加入到该表中的列不会成为该视图的组成部分。 创建带有\ ``LOCAL CHECK OPTION``\ 的视图： .. code:: programlisting 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``\ 创建一个视图： .. code:: programlisting CREATE VIEW pg_comedies AS SELECT * FROM comedies WHERE classification = 'PG' WITH CASCADED CHECK OPTION; 这将创建一个检查新行的\ ``kind``\ 和\ ``classification`` 的视图。 创建一个由可更新列和不可更新列混合而成的视图： .. code:: programlisting 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 组成的递归视图： .. code:: programlisting 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的名称不能是方案限定的。 .. container:: refsect1 :name: id-1.9.3.97.9 .. rubric:: 兼容性 :name: 兼容性 ``CREATE OR REPLACE VIEW``\ 是一种 OushuDB的语言扩展。临时 视图的概念也是这样。\ ``WITH ( ... )``\ 子句也是一种扩展。 .. container:: refsect1 :name: id-1.9.3.97.10 .. rubric:: 另见 :name: 另见 `ALTER VIEW `__, `DROP VIEW `__