Postgresql 中文操作指南

CREATE VIEW

CREATE VIEW - 定义新视图

CREATE VIEW — define a new 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 VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

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

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

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

If a schema name is given (for example, CREATE VIEW myschema.myview …​) then the view is created in the specified schema. Otherwise it is created in the current schema. Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. The name of the view must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema.

Parameters

  • TEMPORARY or TEMP

    • If specified, the view is created as a temporary view. Temporary views are automatically dropped at the end of the current session. Existing permanent relations with the same name are not visible to the current session while the temporary view exists, unless they are referenced with schema-qualified names.

    • If any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMPORARY is specified or not).

  • RECURSIVE

    • Creates a recursive view. The syntax

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;

  • A view column name list must be specified for a recursive view.

    • name

  • The name (optionally schema-qualified) of a view to be created.

    • column_name

  • An optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.

    • WITH ( _view_option_name [= view_option_value] [, …​ ] )_

  • This clause specifies optional parameters for a view; the following parameters are supported:

  • All of the above options can be changed on existing views using ALTER VIEW.

    • query

  • A SELECT or VALUES command which will provide the columns and rows of the view.

    • WITH [ CASCADED | LOCAL ] CHECK OPTION

  • This option controls the behavior of automatically updatable views. When this option is specified, INSERT and UPDATE commands on the view will be checked to ensure that new rows satisfy the view-defining condition (that is, the new rows are checked to ensure that they are visible through the view). If they are not, the update will be rejected. If the CHECK OPTION is not specified, INSERT and UPDATE commands on the view are allowed to create rows that are not visible through the view. The following check options are supported:

  • The CHECK OPTION may not be used with RECURSIVE views.

  • Note that the CHECK OPTION is only supported on views that are automatically updatable, and do not have INSTEAD OF triggers or INSTEAD rules. If an automatically updatable view is defined on top of a base view that has INSTEAD OF triggers, then the LOCAL CHECK OPTION may be used to check the conditions on the automatically updatable view, but the conditions on the base view with INSTEAD OF triggers will not be checked (a cascaded check option will not cascade down to a trigger-updatable view, and any check options defined directly on a trigger-updatable view will be ignored). If the view or any of its base relations has an INSTEAD rule that causes the INSERT or UPDATE command to be rewritten, then all check options will be ignored in the rewritten query, including any checks from automatically updatable views defined on top of the relation with the INSTEAD rule.

    • check_option (enum)

  • This parameter may be either local or cascaded, and is equivalent to specifying WITH [ CASCADED | LOCAL ] CHECK OPTION (see below).

    • security_barrier (boolean)

  • This should be used if the view is intended to provide row-level security. See Section 41.5 for full details.

    • security_invoker (boolean)

  • This option causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner. See the notes below for full details.

    • LOCAL

  • New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION).

    • CASCADED

  • New rows are checked against the conditions of the view and all underlying base views. If the CHECK OPTION is specified, and neither LOCAL nor CASCADED is specified, then CASCADED is assumed.

Notes

使用 DROP VIEW 语句删除视图。

Use the DROP VIEW statement to drop views.

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

Be careful that the names and types of the view’s columns will be assigned the way you want. For example:

CREATE VIEW vista AS SELECT 'Hello World';

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

is bad form because the column name defaults to ?column?; also, the column data type defaults to text, which might not be what you wanted. Better style for a string literal in a view’s result is something like:

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

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

By default, access to the underlying base relations referenced in the view is determined by the permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering; see Section 41.5 for details.

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

If the view has the security_invoker property set to true, access to the underlying base relations is determined by the permissions of the user executing the query, rather than the view owner. Thus, the user of a security invoker view must have the relevant permissions on the view and its underlying base relations.

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

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.

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

If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied, and access to any additional relations referred to by those policies is determined by the permissions of the view owner. However, if the view has security_invoker set to true, then the policies and permissions of the invoking user are used instead, as if the base relations had been referenced directly from the query using the view.

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

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.

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

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 规则，以及任何 WITH ( …​ ) 参数及其 CHECK OPTION 。其他视图属性（包括所有权、权限和非 SELECT 规则）保持不变。您必须拥有此视图才能替换它（这包括成为拥有角色的成员）。

When CREATE OR REPLACE VIEW is used on an existing view, only the view’s defining SELECT rule, plus any WITH ( …​ ) parameters and its CHECK OPTION are changed. Other view properties, including ownership, permissions, and non-SELECT rules, remain unchanged. You must own the view to replace it (this includes being a member of the owning role).

Updatable Views

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

Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

可以自动更新的视图可能包含可更新和不可更新的列的混合。如果某列是基础基本关系的可更新列的简单引用，则该列可更新；否则，该列是只读的，并且如果 INSERT 或 UPDATE 语句尝试向其分配值，则会引发错误。

An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation; otherwise the column is read-only, and an error will be raised if an INSERT or UPDATE statement attempts to assign a value to it.

如果视图可以自动更新，则系统会将视图上的任何 INSERT 、 UPDATE 或 DELETE 语句转换为基础基本关系上的相应语句。具有 ON CONFLICT UPDATE 子句的 INSERT 语句得到完全支持。

If the view is automatically updatable the system will convert any INSERT, UPDATE or DELETE statement on the view into the corresponding statement on the underlying base relation. INSERT statements that have an ON CONFLICT UPDATE clause are fully supported.

如果可自动更新的视图包含 WHERE 条件，则该条件限制基本关系的哪些行可由该视图的 UPDATE 和 DELETE 语句修改。但是，允许 UPDATE 更改一行，使其不再满足 WHERE 条件，因此不再通过该视图可见。类似地， INSERT 命令可能会插入不满足 WHERE 条件的基本关系行，因此不通过该视图可见（ ON CONFLICT UPDATE 可能会类似地影响不通过该视图可见的现有行）。 CHECK OPTION 可用于阻止 INSERT 和 UPDATE 命令创建不通过视图可见的此类行。

If an automatically updatable view contains a WHERE condition, the condition restricts which rows of the base relation are available to be modified by UPDATE and DELETE statements on the view. However, an UPDATE is allowed to change a row so that it no longer satisfies the WHERE condition, and thus is no longer visible through the view. Similarly, an INSERT command can potentially insert base-relation rows that do not satisfy the WHERE condition and thus are not visible through the view (ON CONFLICT UPDATE may similarly affect an existing row not visible through the view). The CHECK OPTION may be used to prevent INSERT and UPDATE commands from creating such rows that are not visible through the view.

如果自动更新视图用 security_barrier 属性标记，那么所有视图的 WHERE 条件（以及使用标记为 LEAKPROOF 的运算符的任何条件）将始终在用户添加的任何条件之前进行评估。有关完整详细信息，请参见 Section 41.5 。请注意，因此，最终没有返回的行（因为它们未通过用户的 WHERE 条件）可能仍会最终被锁定。 EXPLAIN 可用于查看在关系级别应用的条件（因此不会锁定行）以及未应用的条件。

If an automatically updatable view is marked with the security_barrier property then all the view’s WHERE conditions (and any conditions using operators which are marked as LEAKPROOF) will always be evaluated before any conditions that a user of the view has added. See Section 41.5 for full details. Note that, due to this, rows which are not ultimately returned (because they do not pass the user’s WHERE conditions) may still end up being locked. EXPLAIN can be used to see which conditions are applied at the relation level (and therefore do not lock rows) and which are not.

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

A more complex view that does not satisfy all these conditions is read-only by default: the system will not allow an insert, update, or delete on the view. You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly.

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

Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition, by default, the view’s owner must have the relevant privileges on the underlying base relations, whereas the user performing the update does not need any permissions on the underlying base relations (see Section 41.5). However, if the view has security_invoker set to true, the user performing the update, rather than the view owner, must have the relevant privileges on the underlying base relations.

Examples

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

Create a view consisting of all comedy films:

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

这将创建一个视图，包含视图创建时 film 表中的列。尽管 * 被用于创建视图，但稍后添加到表中的列将不属于视图的一部分。

This will create a view containing the columns that are in the film table at the time of view creation. Though * was used to create the view, columns added later to the table will not be part of the view.

使用 LOCAL CHECK OPTION 创建一个视图：

Create a view with 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 将不受检查。

This will create a view based on the comedies view, showing only films with kind = 'Comedy' and classification = 'U'. Any attempt to INSERT or UPDATE a row in the view will be rejected if the new row doesn’t have classification = 'U', but the film kind will not be checked.

使用 CASCADED CHECK OPTION 创建一个视图：

Create a view with CASCADED CHECK OPTION:

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

这将创建一个视图，检查新行的 kind 和 classification 。

This will create a view that checks both the kind and classification of new rows.

使用可更新和不可更新列的组合创建视图：

Create a view with a mix of updatable and non-updatable columns:

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 将只读。

This view will support INSERT, UPDATE and DELETE. All the columns from the films table will be updatable, whereas the computed columns country and avg_rating will be read-only.

创建一个包含从 1 到 100 的数字的递归视图：

Create a recursive view consisting of the numbers from 1 to 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 的名称不能经过架构限定。

Notice that although the recursive view’s name is schema-qualified in this CREATE, its internal self-reference is not schema-qualified. This is because the implicitly-created CTE’s name cannot be schema-qualified.

Compatibility

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

CREATE OR REPLACE VIEW is a PostgreSQL language extension. So is the concept of a temporary view. The WITH ( …​ ) clause is an extension as well, as are security barrier views and security invoker views.

See Also

ALTER VIEW ， DROP VIEW ， CREATE MATERIALIZED VIEW

ALTER VIEW, DROP VIEW, CREATE MATERIALIZED VIEW