NAME

CREATE VIEW - 定義一個視圖

SYNOPSIS

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

DESCRIPTION 描述

CREATE VIEW 定義一個查詢的視圖。 這個視圖不是物理上實際存在（于磁盤）的。具體的說，自動生成一個改寫索引規(guī)則（一個 ON SELECT 規(guī)則）的查詢用以支持在視圖上的檢索。

CREATE OR REPLACE VIEW 類似，不過是如果一個同名的視圖已經存在，那么就替換它。 你只能用一個生成相同字段的新查詢替換一個視圖（也就是說，同樣字段名和數據類型）。

 如果給出了一個模式名（比如，CREATE VIEW myschema.myview ...），那么該視圖是在指定的模式中創(chuàng)建的。 否則它是在當前模式中創(chuàng)建的。 該視圖名字必需和同一模式中任何其它視圖，表，序列或者索引的名字不同。  

PARAMETERS 參數

name

 所要創(chuàng)建的視圖名稱（可以有模式修飾）。

column_name

 一個可選的名字列表，用于當作視圖的字段名。如果沒有給出， 字段名取自查詢。

query

 一個將為視圖提供行和列的查詢（也就是一條 SELECT 語句）。

 請參閱 SELECT [select(7)] 獲取有效查詢的更多信息。

NOTES 注意

 目前，視圖是只讀的：系統將不允許在視圖上插入，更新，或者刪除數據。 你可以通過在視圖上創(chuàng)建把插入等動作重寫為向其它表做合適操作的規(guī)則來實現可更新視圖的效果。 更多信息詳見 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 例子

 創(chuàng)建一個由所有喜劇電影組成的視圖：

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.