CREATE VIEW 中文man页面

NAME

CREATE VIEW - 定义一个视图

SYNOPSIS

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DESCRIPTION 描述

CREATE VIEW 定义一个查询的视图。 这个视图不是物理上实际存在（于磁盘）的。具体的说，自动生成一个改写索引规则（一个 ON SELECT 规则）的查询用以支持在视图上的检索。

CREATE OR REPLACE VIEW 类似，不过是如果一个同名的视图已经存在，那么就替换它。 你只能用一个生成相同字段的新查询替换一个视图（也就是说，同样字段名和数据类型）。

 如果给出了一个模式名（比如，CREATE VIEW myschema.myview ...），那么该视图是在指定的模式中创建的。 否则它是在当前模式中创建的。 该视图名字必需和同一模式中任何其它视图，表，序列或者索引的名字不同。  

PARAMETERS 参数

name

 所要创建的视图名称（可以有模式修饰）。

column_name

 一个可选的名字列表，用于当作视图的字段名。如果没有给出， 字段名取自查询。

query

 一个将为视图提供行和列的查询（也就是一条 SELECT 语句）。

 请参阅 SELECT [select(7)] 获取有效查询的更多信息。

NOTES 注意

 目前，视图是只读的：系统将不允许在视图上插入，更新，或者删除数据。 你可以通过在视图上创建把插入等动作重写为向其它表做合适操作的规则来实现可更新视图的效果。 更多信息详见 CREATE RULE [create_rule(7)].

 使用 DROP VIEW 语句删除视图

 请注意视图字段的名字和类型不一定是你们期望的那样。比如，

CREATE VIEW vista AS SELECT 'Hello World';

 在两个方面很糟糕：字段名缺省是 ?column?，并且字段的数据类型缺省是 unknown。 如果你想视图的结果是一个字串文本，那么用类似下面这样的东西

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

 对视图引用的表的访问的权限由视图的所有者决定。 不过，在视图里调用的函数当作他们直接从使用视图的查询里调用看待。 因此，视图的用户必须有使用视图调用的所有函数的权限。  

EXAMPLES 例子

 创建一个由所有喜剧电影组成的视图：

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

COMPATIBILITY 兼容性

 SQL 标准为 CREATE VIEW 声明了一些附加的功能：

CREATE VIEW name [ ( column [, ...] ) ]
    AS query
    [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]

 完整的SQL命令可选的子句是：

CHECK OPTION

 这个选项用于可更新视图。 所有对视图的INSERT和UPDATE都要经过视图定义条件的校验。 （也就是说，新数据应该可以通过视图看到。）如果没有通过校验，更新将被拒绝。

LOCAL

 对这个视图进行完整性检查。

CASCADE

 对此视图和任何相关视图进行完整性检查。 在既没有声明 CASCADE 也没有声明 LOCAL 时，假设为 CASCADE。

CREATE OR REPLACE VIEW 是 PostgreSQL 的扩展。  

#p#

NAME

CREATE VIEW - define a new view

SYNOPSIS

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DESCRIPTION

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 is similar, but if a view of the same name already exists, it is replaced. You can only replace a view with a new query that generates the identical set of columns (i.e., same column names and data types).

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. The view name must be distinct from the name of any other view, table, sequence, or index in the same schema.  

PARAMETERS

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.

query

A query (that is, a SELECT statement) which will provide the columns and rows of the view.

Refer to SELECT [select(7)] for more information about valid queries.

NOTES

Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE RULE [create_rule(7)].

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';

is bad form in two ways: the column name defaults to ?column?, and the column data type defaults to unknown. If you want a string literal in a view's result, use something like

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

Access to tables referenced in the view is determined by permissions of the view owner. However, 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.  

EXAMPLES

Create a view consisting of all comedy films:

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

COMPATIBILITY

The SQL standard specifies some additional capabilities for the CREATE VIEW statement:

CREATE VIEW name [ ( column [, ...] ) ]
    AS query
    [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]

The optional clauses for the full SQL command are:

CHECK OPTION

This option is to do with updatable views. All INSERT and UPDATE commands on the view will be checked to ensure data satisfy the view-defining condition (that is, the new data would be visible through the view). If they do not, the update will be rejected.

LOCAL

Check for integrity on this view.

CASCADE

Check for integrity on this view and on any dependent view. CASCADE is assumed if neither CASCADE nor LOCAL is specified.

CREATE OR REPLACE VIEW is a PostgreSQL language extension.