{"Entry":{"collection":"sql","key":"create-view","name":"CREATE VIEW","aliases":["createview"],"metadata":{"aliases":["createview"],"changed_in":["7.2","7.3","7.4","8.1","9.2","9.3","9.4"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-createview.htm","to_file":"sql-createview.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE VIEW view [ ( column name list ) ] AS SELECT query"],"removed":[]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query"],"removed":[]},"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query"],"removed":["CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]"],"removed":[]},"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]"],"removed":[]},"to":"9.2"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]"],"removed":[]},"to":"9.3"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]"],"removed":[]},"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"e1893bfe4ddd94f4a518a36903f89579d972445f69fc5e0599a3a5043b4ca43e","editorial":{},"first_version":"6.4","group":"table","imported_at":"2026-09-30T17:43:36.758957+08:00","last_version":"20","name":"CREATE VIEW","object":"VIEW","position":12,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new view","purpose_zh":"","related":["alter-view","drop-view","create-materialized-view"],"slug":"create-view","source_rev":"a709ab85","synopsis":"CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]\n[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]\nAS query\n[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-view","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-view","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEVIEW","file":"sql-createview.html","lang":"en","name":"CREATE VIEW","purpose":"define a new view","purpose_zh":"","related":["alter-view","drop-view","create-materialized-view"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE VIEW\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e 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.\u003c/p\u003e\u003cp\u003eIf a schema name is given (for example, \u003ccode class=\"literal\"\u003eCREATE VIEW myschema.myview ...\u003c/code\u003e) 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.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e or \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf 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.\u003c/p\u003e\u003cp\u003eIf any of the tables referenced by the view are temporary, the view is created as a temporary view (whether \u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e is specified or not).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRECURSIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eCreates a recursive view. The syntax\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCREATE RECURSIVE VIEW [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003eis equivalent to\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCREATE VIEW [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e AS WITH RECURSIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS (SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e) SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003eA view column name list must be specified for a recursive view.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of a view to be created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_name\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_value\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional parameters for a view; the following parameters are supported:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003echeck_option\u003c/code\u003e (\u003ccode class=\"type\"\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis parameter may be either \u003ccode class=\"literal\"\u003elocal\u003c/code\u003e or \u003ccode class=\"literal\"\u003ecascaded\u003c/code\u003e, and is equivalent to specifying \u003ccode class=\"literal\"\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e (see below).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esecurity_barrier\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis should be used if the view is intended to provide row-level security. See \u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. Rules and Privileges\"\u003eSection 39.5\u003c/a\u003e for full details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis 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.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eAll of the above options can be changed on existing views using \u003ca href=\"/docs/18/sql-alterview.html\" title=\"ALTER VIEW\"\u003e\u003ccode class=\"command\"\u003eALTER VIEW\u003c/code\u003e\u003c/a\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e or \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e command which will provide the columns and rows of the view.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis option controls the behavior of automatically updatable views. When this option is specified, \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e is not specified, \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e commands on the view are allowed to create rows that are not visible through the view. The following check options are supported:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eNew 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 \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eNew rows are checked against the conditions of the view and all underlying base views. If the \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e is specified, and neither \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e nor \u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e is specified, then \u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e is assumed.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e may not be used with \u003ccode class=\"literal\"\u003eRECURSIVE\u003c/code\u003e views.\u003c/p\u003e\u003cp\u003eNote that the \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e is only supported on views that are automatically updatable, and do not have \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e triggers or \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rules. If an automatically updatable view is defined on top of a base view that has \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e triggers, then the \u003ccode class=\"literal\"\u003eLOCAL CHECK OPTION\u003c/code\u003e may be used to check the conditions on the automatically updatable view, but the conditions on the base view with \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rule that causes the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e rule. \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e is not supported if the view or any of its base relations have rules.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eUse the \u003ca href=\"/docs/18/sql-dropview.html\" title=\"DROP VIEW\"\u003e\u003ccode class=\"command\"\u003eDROP VIEW\u003c/code\u003e\u003c/a\u003e statement to drop views.\u003c/p\u003e\u003cp\u003eBe careful that the names and types of the view's columns will be assigned the way you want. For example:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW vista AS SELECT 'Hello World';\n\u003c/pre\u003e\u003cp\u003eis bad form because the column name defaults to \u003ccode class=\"literal\"\u003e?column?\u003c/code\u003e; also, the column data type defaults to \u003ccode class=\"type\"\u003etext\u003c/code\u003e, which might not be what you wanted. Better style for a string literal in a view's result is something like:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW vista AS SELECT text 'Hello World' AS hello;\n\u003c/pre\u003e\u003cp\u003eBy 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 \u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. Rules and Privileges\"\u003eSection 39.5\u003c/a\u003e for details.\u003c/p\u003e\u003cp\u003eIf the view has the \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e property set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e, 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.\u003c/p\u003e\u003cp\u003eIf 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 \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e property.\u003c/p\u003e\u003cp\u003eIf any of the underlying base relations has \u003ca href=\"/docs/18/ddl-rowsecurity.html\" title=\"5.9. Row Security Policies\"\u003erow-level security\u003c/a\u003e 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 \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e, 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.\u003c/p\u003e\u003cp\u003eFunctions 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 \u003ccode class=\"literal\"\u003eSECURITY INVOKER\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e. Thus, for example, calling \u003ccode class=\"literal\"\u003eCURRENT_USER\u003c/code\u003e directly in a view will always return the invoking user, not the view owner. This is not affected by the view's \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e setting, and so a view with \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e set to \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e is \u003cspan class=\"emphasis\"\u003e\u003cem\u003enot\u003c/em\u003e\u003c/span\u003e equivalent to a \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e function and those concepts should not be confused.\u003c/p\u003e\u003cp\u003eThe user creating or replacing a view must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the schema containing the view, not on the schemas referred to in the view query, even for a security invoker view.\u003c/p\u003e\u003cp\u003eWhen \u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e is used on an existing view, only the view's defining SELECT rule, plus any \u003ccode class=\"literal\"\u003eWITH ( ... )\u003c/code\u003e parameters and its \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e 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).\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003eUpdatable Views\u003c/h3\u003e\u003cp\u003eSimple views are automatically updatable: the system will allow \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e 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:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe view must have exactly one entry in its \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e list, which must be a table or another updatable view.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe view definition must not contain \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e, \u003ccode class=\"literal\"\u003eDISTINCT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e, \u003ccode class=\"literal\"\u003eHAVING\u003c/code\u003e, \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e clauses at the top level.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe view definition must not contain set operations (\u003ccode class=\"literal\"\u003eUNION\u003c/code\u003e, \u003ccode class=\"literal\"\u003eINTERSECT\u003c/code\u003e or \u003ccode class=\"literal\"\u003eEXCEPT\u003c/code\u003e) at the top level.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe view's select list must not contain any aggregates, window functions or set-returning functions.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003eAn 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 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e statement attempts to assign a value to it.\u003c/p\u003e\u003cp\u003eIf the view is automatically updatable the system will convert any \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e statement on the view into the corresponding statement on the underlying base relation. \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e statements that have an \u003ccode class=\"literal\"\u003eON CONFLICT UPDATE\u003c/code\u003e clause are fully supported.\u003c/p\u003e\u003cp\u003eIf an automatically updatable view contains a \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition, the condition restricts which rows of the base relation are available to be modified by \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e statements on the view. However, an \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e or \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e is allowed to change a row so that it no longer satisfies the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition, and thus is no longer visible through the view. Similarly, an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e command can potentially insert base-relation rows that do not satisfy the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition and thus are not visible through the view (\u003ccode class=\"literal\"\u003eON CONFLICT UPDATE\u003c/code\u003e may similarly affect an existing row not visible through the view). The \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e may be used to prevent \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e commands from creating such rows that are not visible through the view.\u003c/p\u003e\u003cp\u003eIf an automatically updatable view is marked with the \u003ccode class=\"literal\"\u003esecurity_barrier\u003c/code\u003e property then all the view's \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e conditions (and any conditions using operators which are marked as \u003ccode class=\"literal\"\u003eLEAKPROOF\u003c/code\u003e) will always be evaluated before any conditions that a user of the view has added. See \u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. Rules and Privileges\"\u003eSection 39.5\u003c/a\u003e for full details. Note that, due to this, rows which are not ultimately returned (because they do not pass the user's \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e conditions) may still end up being locked. \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e can be used to see which conditions are applied at the relation level (and therefore do not lock rows) and which are not.\u003c/p\u003e\u003cp\u003eA more complex view that does not satisfy all these conditions is read-only by default: the system will not allow an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e on the view. You can get the effect of an updatable view by creating \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see \u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e. Another possibility is to create rules (see \u003ca href=\"/docs/18/sql-createrule.html\" title=\"CREATE RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE RULE\u003c/span\u003e\u003c/a\u003e), but in practice triggers are easier to understand and use correctly. Also note that \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e is not supported on relations with rules.\u003c/p\u003e\u003cp\u003eNote 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 \u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. Rules and Privileges\"\u003eSection 39.5\u003c/a\u003e). However, if the view has \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e, the user performing the update, rather than the view owner, must have the relevant privileges on the underlying base relations.\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate a view consisting of all comedy films:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW comedies AS\n    SELECT *\n    FROM films\n    WHERE kind = 'Comedy';\n\u003c/pre\u003e\u003cp\u003eThis will create a view containing the columns that are in the \u003ccode class=\"literal\"\u003efilm\u003c/code\u003e table at the time of view creation. Though \u003ccode class=\"literal\"\u003e*\u003c/code\u003e was used to create the view, columns added later to the table will not be part of the view.\u003c/p\u003e\u003cp\u003eCreate a view with \u003ccode class=\"literal\"\u003eLOCAL CHECK OPTION\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW universal_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = 'U'\n    WITH LOCAL CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003eThis will create a view based on the \u003ccode class=\"literal\"\u003ecomedies\u003c/code\u003e view, showing only films with \u003ccode class=\"literal\"\u003ekind = 'Comedy'\u003c/code\u003e and \u003ccode class=\"literal\"\u003eclassification = 'U'\u003c/code\u003e. Any attempt to \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e a row in the view will be rejected if the new row doesn't have \u003ccode class=\"literal\"\u003eclassification = 'U'\u003c/code\u003e, but the film \u003ccode class=\"literal\"\u003ekind\u003c/code\u003e will not be checked.\u003c/p\u003e\u003cp\u003eCreate a view with \u003ccode class=\"literal\"\u003eCASCADED CHECK OPTION\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW pg_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = 'PG'\n    WITH CASCADED CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003eThis will create a view that checks both the \u003ccode class=\"literal\"\u003ekind\u003c/code\u003e and \u003ccode class=\"literal\"\u003eclassification\u003c/code\u003e of new rows.\u003c/p\u003e\u003cp\u003eCreate a view with a mix of updatable and non-updatable columns:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW comedies AS\n    SELECT f.*,\n           country_code_to_name(f.country_code) AS country,\n           (SELECT avg(r.rating)\n            FROM user_ratings r\n            WHERE r.film_id = f.id) AS avg_rating\n    FROM films f\n    WHERE f.kind = 'Comedy';\n\u003c/pre\u003e\u003cp\u003eThis view will support \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e and \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e. All the columns from the \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e table will be updatable, whereas the computed columns \u003ccode class=\"literal\"\u003ecountry\u003c/code\u003e and \u003ccode class=\"literal\"\u003eavg_rating\u003c/code\u003e will be read-only.\u003c/p\u003e\u003cp\u003eCreate a recursive view consisting of the numbers from 1 to 100:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RECURSIVE VIEW public.nums_1_100 (n) AS\n    VALUES (1)\nUNION ALL\n    SELECT n+1 FROM nums_1_100 WHERE n \u0026lt; 100;\n\u003c/pre\u003e\u003cp\u003eNotice that although the recursive view's name is schema-qualified in this \u003ccode class=\"command\"\u003eCREATE\u003c/code\u003e, its internal self-reference is not schema-qualified. This is because the implicitly-created CTE's name cannot be schema-qualified.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e language extension. So is the concept of a temporary view. The \u003ccode class=\"literal\"\u003eWITH ( ... )\u003c/code\u003e clause is an extension as well, as are security barrier views and security invoker views.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-view/?v=18\" title=\"ALTER VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-view/?v=18\" title=\"DROP VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_name\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_value\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]","synopsis_text":"CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]\n[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]\nAS query\n[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-view","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE VIEW","Summary":"定义一个新视图","BodyHTML":"\u003cpre\u003eCREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]\n[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]\nAS query\n[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE VIEW\u003c/code\u003e定义一个视图，其内容来自一个查询。该视图不会被实际物化。相反，每次在查询中引用该视图时，都会执行定义它的查询。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE OR REPLACE VIEW\u003c/code\u003e与之类似，但如果已经存在同名视图，则会用新定义替换它。新查询必须生成与现有视图查询相同的列（即列名、列顺序和数据类型都相同），但可以在列表末尾附加额外的列。生成输出列的计算方式则可以完全不同。\u003c/p\u003e\u003cp\u003e如果给出了模式名称（例如，\u003ccode\u003eCREATE VIEW myschema.myview ...\u003c/code\u003e），则视图将在指定的模式中创建。否则，它将在当前模式中创建。临时视图存在于一个特殊模式中，因此在创建临时视图时不能给出模式名称。视图的名称必须与同一模式中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称不同。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该选项，视图将被创建为临时视图。临时视图会在当前会话结束时自动删除。当临时视图存在时，除非使用模式限定名称引用，否则具有相同名称的现有永久关系对当前会话不可见。\u003c/p\u003e\u003cp\u003e如果视图引用的任何表是临时表，则该视图也会被创建为临时视图（无论是否指定了\u003ccode\u003eTEMPORARY\u003c/code\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eRECURSIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e创建一个递归视图。语法\u003c/p\u003e\u003cpre\u003eCREATE RECURSIVE VIEW [ \u003cem\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS SELECT \u003cem\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e等效于\u003c/p\u003e\u003cpre\u003eCREATE VIEW [ \u003cem\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e AS WITH RECURSIVE \u003cem\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS (SELECT \u003cem\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e) SELECT \u003cem\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e FROM \u003cem\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e递归视图必须指定视图列名列表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的视图名称（可以带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e视图各列使用的名称列表，可选。若未给出，则从查询中推断列名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH ( \u003cem\u003e\u003ccode\u003eview_option_name\u003c/code\u003e\u003c/em\u003e [= \u003cem\u003e\u003ccode\u003eview_option_value\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句为视图指定可选参数；支持以下参数：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003echeck_option\u003c/code\u003e (\u003ccode\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此参数可以是\u003ccode\u003elocal\u003c/code\u003e或\u003ccode\u003ecascaded\u003c/code\u003e，等同于指定 \u003ccode\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e（见下文）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003esecurity_barrier\u003c/code\u003e (\u003ccode\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果该视图旨在提供行级安全，则应使用此选项。详见\u003ca href=\"/docs/18/rules-privileges.html\" rel=\"nofollow\"\u003e第 39.5 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003esecurity_invoker\u003c/code\u003e (\u003ccode\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该选项会根据视图使用者而非视图所有者的权限来检查底层基关系。完整细节见下文注解。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e以上所有选项都可以通过\u003ca href=\"/docs/18/sql-alterview.html\" title=\"ALTER VIEW\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER VIEW\u003c/code\u003e\u003c/a\u003e 在现有视图上修改。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个将为该视图提供列和行的\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\" rel=\"nofollow\"\u003e\u003ccode\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该选项控制自动可更新视图的行为。指定该选项时，会检查对该视图执行的 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e和\u003ccode\u003eMERGE\u003c/code\u003e 命令，以确保新行满足视图定义条件（即确保能够通过该视图看到这些新行）。如果不满足条件，该修改将被拒绝。若未指定 \u003ccode\u003eCHECK OPTION\u003c/code\u003e，则允许在该视图上执行 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e和\u003ccode\u003eMERGE\u003c/code\u003e 命令来创建通过该视图不可见的行。支持以下检查选项：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新行只会根据该视图自身直接定义的条件进行检查。底层基视图上定义的任何条件都不会被检查（除非它们也指定了 \u003ccode\u003eCHECK OPTION\u003c/code\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCASCADED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新行会根据该视图及所有底层基视图的条件进行检查。如果指定了 \u003ccode\u003eCHECK OPTION\u003c/code\u003e，但既未指定 \u003ccode\u003eLOCAL\u003c/code\u003e也未指定\u003ccode\u003eCASCADED\u003c/code\u003e，则假定为\u003ccode\u003eCASCADED\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e\u003ccode\u003eCHECK OPTION\u003c/code\u003e不能与\u003ccode\u003eRECURSIVE\u003c/code\u003e视图一起使用。\u003c/p\u003e\u003cp\u003e注意，\u003ccode\u003eCHECK OPTION\u003c/code\u003e仅支持自动可更新且不带 \u003ccode\u003eINSTEAD OF\u003c/code\u003e触发器或\u003ccode\u003eINSTEAD\u003c/code\u003e 规则的视图。如果一个自动可更新视图定义在带有 \u003ccode\u003eINSTEAD OF\u003c/code\u003e触发器的基视图之上，则可以使用 \u003ccode\u003eLOCAL CHECK OPTION\u003c/code\u003e检查该自动可更新视图自身的条件，但不会检查带有\u003ccode\u003eINSTEAD OF\u003c/code\u003e触发器的基视图上的条件（级联检查选项不会继续向下级联到触发器可更新视图，而直接定义在触发器可更新视图上的任何检查选项也都会被忽略）。如果该视图或其任何基关系带有会导致\u003ccode\u003eINSERT\u003c/code\u003e或\u003ccode\u003eUPDATE\u003c/code\u003e 命令被重写的\u003ccode\u003eINSTEAD\u003c/code\u003e规则，则在重写后的查询中，所有检查选项都会被忽略，包括来自定义在带有 \u003ccode\u003eINSTEAD\u003c/code\u003e规则的关系之上的自动可更新视图的任何检查。若该视图或其任何基关系带有规则，则不支持 \u003ccode\u003eMERGE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e使用\u003ca href=\"/docs/18/sql-dropview.html\" title=\"DROP VIEW\" rel=\"nofollow\"\u003e\u003ccode\u003eDROP VIEW\u003c/code\u003e\u003c/a\u003e语句删除视图。\u003c/p\u003e\u003cp\u003e应留意视图列的名称和类型是否按预期确定。例如：\u003c/p\u003e\u003cpre\u003eCREATE VIEW vista AS SELECT \u0026#39;Hello World\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e这种写法不好，因为列名默认是\u003ccode\u003e?column?\u003c/code\u003e；而且列数据类型默认是\u003ccode\u003etext\u003c/code\u003e，这可能并非所需。若要在视图结果中使用字符串字面量，更好的写法类似于：\u003c/p\u003e\u003cpre\u003eCREATE VIEW vista AS SELECT text \u0026#39;Hello World\u0026#39; AS hello;\n\u003c/pre\u003e\u003cp\u003e默认情况下，访问视图中引用的底层基关系时，会依据视图所有者的权限进行判断。在某些情况下，这可用于为底层表提供安全但受限的访问。然而，并非所有视图都能防止被篡改；详见\u003ca href=\"/docs/18/rules-privileges.html\" rel=\"nofollow\"\u003e第 39.5 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果视图的\u003ccode\u003esecurity_invoker\u003c/code\u003e属性设置为\u003ccode\u003etrue\u003c/code\u003e，则访问底层基关系时依据的是执行查询的用户的权限，而非视图所有者的权限。因此，安全调用者视图的使用者必须对该视图及其底层基关系拥有相应权限。\u003c/p\u003e\u003cp\u003e如果任何底层基关系本身是安全调用者视图，则它会被视为是由原始查询直接访问的。因此，安全调用者视图总会使用当前用户的权限来检查其底层基关系，即使它是从不带\u003ccode\u003esecurity_invoker\u003c/code\u003e属性的视图中被访问的。\u003c/p\u003e\u003cp\u003e如果任何底层基关系启用了\u003ca href=\"/docs/18/ddl-rowsecurity.html\" rel=\"nofollow\"\u003e行级安全\u003c/a\u003e，那么默认会应用视图所有者的行级安全策略，而这些策略所引用的任何额外关系，其访问权限也由视图所有者的权限决定。然而，如果视图的 \u003ccode\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode\u003etrue\u003c/code\u003e，则改为使用调用用户的策略和权限，就如同这些基关系是通过使用该视图的查询被直接引用的一样。\u003c/p\u003e\u003cp\u003e视图中调用的函数，其处理方式与在使用该视图的查询中直接调用这些函数相同。因此，视图的使用者必须拥有调用视图所用全部函数的权限。视图中的函数会以执行查询的用户或函数所有者的权限执行，这取决于这些函数被定义为\u003ccode\u003eSECURITY INVOKER\u003c/code\u003e还是 \u003ccode\u003eSECURITY DEFINER\u003c/code\u003e。因此，例如，在视图中直接调用 \u003ccode\u003eCURRENT_USER\u003c/code\u003e将始终返回调用用户，而不是视图所有者。这一点不受视图的\u003ccode\u003esecurity_invoker\u003c/code\u003e设置影响，因此，\u003ccode\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode\u003efalse\u003c/code\u003e的视图\u003cspan\u003e\u003cem\u003e并不\u003c/em\u003e\u003c/span\u003e等同于 \u003ccode\u003eSECURITY DEFINER\u003c/code\u003e函数，不应混淆这两个概念。\u003c/p\u003e\u003cp\u003e创建或替换视图的用户必须在视图查询所引用的每个模式上拥有 \u003ccode\u003eUSAGE\u003c/code\u003e权限，以便在这些模式中查找被引用的对象。但请注意，这种查找只会在创建或替换视图时发生。因此，视图的使用者只需要对包含该视图的模式拥有\u003ccode\u003eUSAGE\u003c/code\u003e权限，而不需要对视图查询中引用的模式拥有该权限，即使对于安全调用者视图也是如此。\u003c/p\u003e\u003cp\u003e当对现有视图使用\u003ccode\u003eCREATE OR REPLACE VIEW\u003c/code\u003e时，只会更改视图定义的\u003ccode\u003eSELECT\u003c/code\u003e规则，以及任何 \u003ccode\u003eWITH ( ... )\u003c/code\u003e参数和其\u003ccode\u003eCHECK OPTION\u003c/code\u003e。其他视图属性，包括所有权、权限和非\u003ccode\u003eSELECT\u003c/code\u003e规则，都保持不变。要替换该视图，你必须拥有它（这也包括成为拥有该视图的角色的成员）。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e可更新视图\u003c/h3\u003e\u003cp\u003e简单视图是自动可更新的：系统允许像对普通表一样在视图上使用 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e和\u003ccode\u003eMERGE\u003c/code\u003e语句。满足下列所有条件的视图是自动可更新的：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e该视图的\u003ccode\u003eFROM\u003c/code\u003e列表中必须恰好只有一项，而且这一项必须是一个表或另一个可更新视图。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e视图定义的顶层不能包含\u003ccode\u003eWITH\u003c/code\u003e、\u003ccode\u003eDISTINCT\u003c/code\u003e、\u003ccode\u003eGROUP BY\u003c/code\u003e、\u003ccode\u003eHAVING\u003c/code\u003e、\u003ccode\u003eLIMIT\u003c/code\u003e或者\u003ccode\u003eOFFSET\u003c/code\u003e子句。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e视图定义的顶层不能包含集合操作（\u003ccode\u003eUNION\u003c/code\u003e、\u003ccode\u003eINTERSECT\u003c/code\u003e或者\u003ccode\u003eEXCEPT\u003c/code\u003e）。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e视图的\u003ccode\u003eSELECT\u003c/code\u003e列表不能包含任何聚合函数、窗口函数或集合返回函数。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e自动可更新视图可以同时包含可更新列和不可更新列。如果某列只是简单引用了底层基关系中的一个可更新列，则该列是可更新的；否则，该列为只读，如果\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e或 \u003ccode\u003eMERGE\u003c/code\u003e语句试图为其赋值，就会报错。\u003c/p\u003e\u003cp\u003e如果视图是自动可更新的，系统会把视图上的任何 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e或\u003ccode\u003eMERGE\u003c/code\u003e语句转换为底层基关系上的对应语句。带有\u003ccode\u003eON CONFLICT UPDATE\u003c/code\u003e子句的 \u003ccode\u003eINSERT\u003c/code\u003e语句也得到完全支持。\u003c/p\u003e\u003cp\u003e如果自动可更新视图包含\u003ccode\u003eWHERE\u003c/code\u003e条件，那么在该视图上执行\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e和 \u003ccode\u003eMERGE\u003c/code\u003e语句时，该条件会限制底层基关系中哪些行可被修改。不过，仍允许\u003ccode\u003eUPDATE\u003c/code\u003e或\u003ccode\u003eMERGE\u003c/code\u003e把某一行改成不再满足\u003ccode\u003eWHERE\u003c/code\u003e条件，从而使其不再能通过该视图看到。类似地，\u003ccode\u003eINSERT\u003c/code\u003e或 \u003ccode\u003eMERGE\u003c/code\u003e命令也可能插入不满足 \u003ccode\u003eWHERE\u003c/code\u003e条件的基关系行，因此这些行通过该视图不可见（\u003ccode\u003eON CONFLICT UPDATE\u003c/code\u003e也可能类似地影响现有的、通过该视图不可见的行）。可以使用\u003ccode\u003eCHECK OPTION\u003c/code\u003e阻止 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e和 \u003ccode\u003eMERGE\u003c/code\u003e命令创建这类通过该视图不可见的行。\u003c/p\u003e\u003cp\u003e如果自动可更新视图带有\u003ccode\u003esecurity_barrier\u003c/code\u003e属性，那么视图上的所有\u003ccode\u003eWHERE\u003c/code\u003e条件（以及任何使用标记为 \u003ccode\u003eLEAKPROOF\u003c/code\u003e的操作符的条件）总会先于视图使用者添加的任何条件求值。详见\u003ca href=\"/docs/18/rules-privileges.html\" rel=\"nofollow\"\u003e第 39.5 节\u003c/a\u003e。请注意，因此那些最终不会返回的行（因为它们未通过用户的\u003ccode\u003eWHERE\u003c/code\u003e条件）仍可能被锁定。可以使用\u003ccode\u003eEXPLAIN\u003c/code\u003e查看哪些条件是在关系级别应用的（因而不会锁定行），哪些则不是。\u003c/p\u003e\u003cp\u003e默认情况下，更复杂且不满足上述全部条件的视图是只读的：系统不允许对该视图执行\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e或\u003ccode\u003eMERGE\u003c/code\u003e。可以通过在该视图上创建\u003ccode\u003eINSTEAD OF\u003c/code\u003e触发器来获得可更新视图的效果，这类触发器必须把针对该视图的插入等操作转换为在其他表上执行的适当动作。更多信息请见\u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e。另一种可能性是创建规则（见\u003ca href=\"/docs/18/sql-createrule.html\" title=\"CREATE RULE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE RULE\u003c/span\u003e\u003c/a\u003e），但在实践中，触发器更容易理解和正确使用。另外请注意，带有规则的关系不支持 \u003ccode\u003eMERGE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e注意，在视图上执行插入、更新或删除的用户，必须拥有该视图上相应的插入、更新或删除权限。另外，默认情况下，视图所有者必须在底层基关系上拥有相关权限，而执行更新的用户则不需要在底层基关系上拥有任何权限（参见\u003ca href=\"/docs/18/rules-privileges.html\" rel=\"nofollow\"\u003e第 39.5 节\u003c/a\u003e）。但是，如果视图的 \u003ccode\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode\u003etrue\u003c/code\u003e，则必须由执行更新的用户而非视图所有者，在底层基关系上拥有相关权限。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e创建一个由所有喜剧影片组成的视图：\u003c/p\u003e\u003cpre\u003eCREATE VIEW comedies AS\n    SELECT *\n    FROM films\n    WHERE kind = \u0026#39;Comedy\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e这会创建一个视图，其中包含创建视图时\u003ccode\u003efilms\u003c/code\u003e表中的各列。尽管创建视图时使用了\u003ccode\u003e*\u003c/code\u003e，但以后添加到该表的列不会成为该视图的一部分。\u003c/p\u003e\u003cp\u003e创建带有\u003ccode\u003eLOCAL CHECK OPTION\u003c/code\u003e的视图：\u003c/p\u003e\u003cpre\u003eCREATE VIEW universal_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = \u0026#39;U\u0026#39;\n    WITH LOCAL CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003e这会创建一个基于视图\u003ccode\u003ecomedies\u003c/code\u003e的视图，只显示 \u003ccode\u003ekind = \u0026#39;Comedy\u0026#39;\u003c/code\u003e且 \u003ccode\u003eclassification = \u0026#39;U\u0026#39;\u003c/code\u003e的影片。如果新行不满足 \u003ccode\u003eclassification = \u0026#39;U\u0026#39;\u003c/code\u003e，则任何对该视图执行 \u003ccode\u003eINSERT\u003c/code\u003e或\u003ccode\u003eUPDATE\u003c/code\u003e的尝试都会被拒绝，但不会检查影片的\u003ccode\u003ekind\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e创建带有\u003ccode\u003eCASCADED CHECK OPTION\u003c/code\u003e的视图：\u003c/p\u003e\u003cpre\u003eCREATE VIEW pg_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = \u0026#39;PG\u0026#39;\n    WITH CASCADED CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003e这会创建一个同时检查新行\u003ccode\u003ekind\u003c/code\u003e和 \u003ccode\u003eclassification\u003c/code\u003e的视图。\u003c/p\u003e\u003cp\u003e创建一个同时包含可更新列和不可更新列的视图：\u003c/p\u003e\u003cpre\u003eCREATE VIEW comedies AS\n    SELECT f.*,\n           country_code_to_name(f.country_code) AS country,\n           (SELECT avg(r.rating)\n            FROM user_ratings r\n            WHERE r.film_id = f.id) AS avg_rating\n    FROM films f\n    WHERE f.kind = \u0026#39;Comedy\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e该视图将支持\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e和 \u003ccode\u003eDELETE\u003c/code\u003e。所有来自\u003ccode\u003efilms\u003c/code\u003e表的列都可更新，而计算列\u003ccode\u003ecountry\u003c/code\u003e和 \u003ccode\u003eavg_rating\u003c/code\u003e为只读。\u003c/p\u003e\u003cp\u003e创建一个由1到100的数字组成的递归视图：\u003c/p\u003e\u003cpre\u003eCREATE RECURSIVE VIEW public.nums_1_100 (n) AS\n    VALUES (1)\nUNION ALL\n    SELECT n+1 FROM nums_1_100 WHERE n \u0026lt; 100;\n\u003c/pre\u003e\u003cp\u003e请注意，尽管在这个\u003ccode\u003eCREATE\u003c/code\u003e命令中递归视图名称带有模式限定，但其内部自引用并没有带模式限定。这是因为隐式创建的 CTE 名称不能带模式限定。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE OR REPLACE VIEW\u003c/code\u003e是 \u003cspan\u003ePostgreSQL\u003c/span\u003e的语言扩展。临时视图的概念也是如此。\u003ccode\u003eWITH ( ... )\u003c/code\u003e子句同样是扩展，安全屏障视图和安全调用者视图也是如此。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-view/?v=18\" title=\"ALTER VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-view/?v=18\" title=\"DROP VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"53cf6d7f689d59f3530c06bde7c0f484e7773b61fbca85a5ee7a984bcf5ff8d9","Payload":{"purpose_zh":"定义一个新视图","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE VIEW\u003c/code\u003e定义一个视图，其内容来自一个查询。该视图不会被实际物化。相反，每次在查询中引用该视图时，都会执行定义它的查询。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e与之类似，但如果已经存在同名视图，则会用新定义替换它。新查询必须生成与现有视图查询相同的列（即列名、列顺序和数据类型都相同），但可以在列表末尾附加额外的列。生成输出列的计算方式则可以完全不同。\u003c/p\u003e\u003cp\u003e如果给出了模式名称（例如，\u003ccode class=\"literal\"\u003eCREATE VIEW myschema.myview ...\u003c/code\u003e），则视图将在指定的模式中创建。否则，它将在当前模式中创建。临时视图存在于一个特殊模式中，因此在创建临时视图时不能给出模式名称。视图的名称必须与同一模式中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称不同。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该选项，视图将被创建为临时视图。临时视图会在当前会话结束时自动删除。当临时视图存在时，除非使用模式限定名称引用，否则具有相同名称的现有永久关系对当前会话不可见。\u003c/p\u003e\u003cp\u003e如果视图引用的任何表是临时表，则该视图也会被创建为临时视图（无论是否指定了\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eRECURSIVE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e创建一个递归视图。语法\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCREATE RECURSIVE VIEW [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e等效于\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCREATE VIEW [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema\u003c/code\u003e\u003c/em\u003e . ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e AS WITH RECURSIVE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e) AS (SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003e...\u003c/code\u003e\u003c/em\u003e) SELECT \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_names\u003c/code\u003e\u003c/em\u003e FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_name\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e递归视图必须指定视图列名列表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的视图名称（可以带模式限定）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e视图各列使用的名称列表，可选。若未给出，则从查询中推断列名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_name\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_value\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句为视图指定可选参数；支持以下参数：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003echeck_option\u003c/code\u003e (\u003ccode class=\"type\"\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e此参数可以是\u003ccode class=\"literal\"\u003elocal\u003c/code\u003e或\u003ccode class=\"literal\"\u003ecascaded\u003c/code\u003e，等同于指定 \u003ccode class=\"literal\"\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e（见下文）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esecurity_barrier\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果该视图旨在提供行级安全，则应使用此选项。详见\u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. 规则和权限\"\u003e第 39.5 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该选项会根据视图使用者而非视图所有者的权限来检查底层基关系。完整细节见下文注解。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e以上所有选项都可以通过\u003ca href=\"/docs/18/sql-alterview.html\" title=\"ALTER VIEW\"\u003e\u003ccode class=\"command\"\u003eALTER VIEW\u003c/code\u003e\u003c/a\u003e 在现有视图上修改。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个将为该视图提供列和行的\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ CASCADED | LOCAL ] CHECK OPTION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该选项控制自动可更新视图的行为。指定该选项时，会检查对该视图执行的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e 命令，以确保新行满足视图定义条件（即确保能够通过该视图看到这些新行）。如果不满足条件，该修改将被拒绝。若未指定 \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e，则允许在该视图上执行 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e 命令来创建通过该视图不可见的行。支持以下检查选项：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新行只会根据该视图自身直接定义的条件进行检查。底层基视图上定义的任何条件都不会被检查（除非它们也指定了 \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新行会根据该视图及所有底层基视图的条件进行检查。如果指定了 \u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e，但既未指定 \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e也未指定\u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e，则假定为\u003ccode class=\"literal\"\u003eCASCADED\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e不能与\u003ccode class=\"literal\"\u003eRECURSIVE\u003c/code\u003e视图一起使用。\u003c/p\u003e\u003cp\u003e注意，\u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e仅支持自动可更新且不带 \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e触发器或\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e 规则的视图。如果一个自动可更新视图定义在带有 \u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e触发器的基视图之上，则可以使用 \u003ccode class=\"literal\"\u003eLOCAL CHECK OPTION\u003c/code\u003e检查该自动可更新视图自身的条件，但不会检查带有\u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e触发器的基视图上的条件（级联检查选项不会继续向下级联到触发器可更新视图，而直接定义在触发器可更新视图上的任何检查选项也都会被忽略）。如果该视图或其任何基关系带有会导致\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e或\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e 命令被重写的\u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则，则在重写后的查询中，所有检查选项都会被忽略，包括来自定义在带有 \u003ccode class=\"literal\"\u003eINSTEAD\u003c/code\u003e规则的关系之上的自动可更新视图的任何检查。若该视图或其任何基关系带有规则，则不支持 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e使用\u003ca href=\"/docs/18/sql-dropview.html\" title=\"DROP VIEW\"\u003e\u003ccode class=\"command\"\u003eDROP VIEW\u003c/code\u003e\u003c/a\u003e语句删除视图。\u003c/p\u003e\u003cp\u003e应留意视图列的名称和类型是否按预期确定。例如：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW vista AS SELECT 'Hello World';\n\u003c/pre\u003e\u003cp\u003e这种写法不好，因为列名默认是\u003ccode class=\"literal\"\u003e?column?\u003c/code\u003e；而且列数据类型默认是\u003ccode class=\"type\"\u003etext\u003c/code\u003e，这可能并非所需。若要在视图结果中使用字符串字面量，更好的写法类似于：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW vista AS SELECT text 'Hello World' AS hello;\n\u003c/pre\u003e\u003cp\u003e默认情况下，访问视图中引用的底层基关系时，会依据视图所有者的权限进行判断。在某些情况下，这可用于为底层表提供安全但受限的访问。然而，并非所有视图都能防止被篡改；详见\u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. 规则和权限\"\u003e第 39.5 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果视图的\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e属性设置为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e，则访问底层基关系时依据的是执行查询的用户的权限，而非视图所有者的权限。因此，安全调用者视图的使用者必须对该视图及其底层基关系拥有相应权限。\u003c/p\u003e\u003cp\u003e如果任何底层基关系本身是安全调用者视图，则它会被视为是由原始查询直接访问的。因此，安全调用者视图总会使用当前用户的权限来检查其底层基关系，即使它是从不带\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e属性的视图中被访问的。\u003c/p\u003e\u003cp\u003e如果任何底层基关系启用了\u003ca href=\"/docs/18/ddl-rowsecurity.html\" title=\"5.9. 行安全性策略\"\u003e行级安全\u003c/a\u003e，那么默认会应用视图所有者的行级安全策略，而这些策略所引用的任何额外关系，其访问权限也由视图所有者的权限决定。然而，如果视图的 \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e，则改为使用调用用户的策略和权限，就如同这些基关系是通过使用该视图的查询被直接引用的一样。\u003c/p\u003e\u003cp\u003e视图中调用的函数，其处理方式与在使用该视图的查询中直接调用这些函数相同。因此，视图的使用者必须拥有调用视图所用全部函数的权限。视图中的函数会以执行查询的用户或函数所有者的权限执行，这取决于这些函数被定义为\u003ccode class=\"literal\"\u003eSECURITY INVOKER\u003c/code\u003e还是 \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e。因此，例如，在视图中直接调用 \u003ccode class=\"literal\"\u003eCURRENT_USER\u003c/code\u003e将始终返回调用用户，而不是视图所有者。这一点不受视图的\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e设置影响，因此，\u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e的视图\u003cspan class=\"emphasis\"\u003e\u003cem\u003e并不\u003c/em\u003e\u003c/span\u003e等同于 \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e函数，不应混淆这两个概念。\u003c/p\u003e\u003cp\u003e创建或替换视图的用户必须在视图查询所引用的每个模式上拥有 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e权限，以便在这些模式中查找被引用的对象。但请注意，这种查找只会在创建或替换视图时发生。因此，视图的使用者只需要对包含该视图的模式拥有\u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e权限，而不需要对视图查询中引用的模式拥有该权限，即使对于安全调用者视图也是如此。\u003c/p\u003e\u003cp\u003e当对现有视图使用\u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e时，只会更改视图定义的\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e规则，以及任何 \u003ccode class=\"literal\"\u003eWITH ( ... )\u003c/code\u003e参数和其\u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e。其他视图属性，包括所有权、权限和非\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e规则，都保持不变。要替换该视图，你必须拥有它（这也包括成为拥有该视图的角色的成员）。\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003e可更新视图\u003c/h3\u003e\u003cp\u003e简单视图是自动可更新的：系统允许像对普通表一样在视图上使用 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e和\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e语句。满足下列所有条件的视图是自动可更新的：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e该视图的\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e列表中必须恰好只有一项，而且这一项必须是一个表或另一个可更新视图。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e视图定义的顶层不能包含\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e、\u003ccode class=\"literal\"\u003eDISTINCT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eGROUP BY\u003c/code\u003e、\u003ccode class=\"literal\"\u003eHAVING\u003c/code\u003e、\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e或者\u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e子句。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e视图定义的顶层不能包含集合操作（\u003ccode class=\"literal\"\u003eUNION\u003c/code\u003e、\u003ccode class=\"literal\"\u003eINTERSECT\u003c/code\u003e或者\u003ccode class=\"literal\"\u003eEXCEPT\u003c/code\u003e）。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e视图的\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e列表不能包含任何聚合函数、窗口函数或集合返回函数。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e自动可更新视图可以同时包含可更新列和不可更新列。如果某列只是简单引用了底层基关系中的一个可更新列，则该列是可更新的；否则，该列为只读，如果\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e语句试图为其赋值，就会报错。\u003c/p\u003e\u003cp\u003e如果视图是自动可更新的，系统会把视图上的任何 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e或\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e语句转换为底层基关系上的对应语句。带有\u003ccode class=\"literal\"\u003eON CONFLICT UPDATE\u003c/code\u003e子句的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e语句也得到完全支持。\u003c/p\u003e\u003cp\u003e如果自动可更新视图包含\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件，那么在该视图上执行\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e和 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e语句时，该条件会限制底层基关系中哪些行可被修改。不过，仍允许\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e把某一行改成不再满足\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件，从而使其不再能通过该视图看到。类似地，\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e或 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e命令也可能插入不满足 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件的基关系行，因此这些行通过该视图不可见（\u003ccode class=\"literal\"\u003eON CONFLICT UPDATE\u003c/code\u003e也可能类似地影响现有的、通过该视图不可见的行）。可以使用\u003ccode class=\"literal\"\u003eCHECK OPTION\u003c/code\u003e阻止 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e命令创建这类通过该视图不可见的行。\u003c/p\u003e\u003cp\u003e如果自动可更新视图带有\u003ccode class=\"literal\"\u003esecurity_barrier\u003c/code\u003e属性，那么视图上的所有\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件（以及任何使用标记为 \u003ccode class=\"literal\"\u003eLEAKPROOF\u003c/code\u003e的操作符的条件）总会先于视图使用者添加的任何条件求值。详见\u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. 规则和权限\"\u003e第 39.5 节\u003c/a\u003e。请注意，因此那些最终不会返回的行（因为它们未通过用户的\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件）仍可能被锁定。可以使用\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e查看哪些条件是在关系级别应用的（因而不会锁定行），哪些则不是。\u003c/p\u003e\u003cp\u003e默认情况下，更复杂且不满足上述全部条件的视图是只读的：系统不允许对该视图执行\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e或\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e。可以通过在该视图上创建\u003ccode class=\"literal\"\u003eINSTEAD OF\u003c/code\u003e触发器来获得可更新视图的效果，这类触发器必须把针对该视图的插入等操作转换为在其他表上执行的适当动作。更多信息请见\u003ca href=\"/docs/18/sql-createtrigger.html\" title=\"CREATE TRIGGER\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRIGGER\u003c/span\u003e\u003c/a\u003e。另一种可能性是创建规则（见\u003ca href=\"/docs/18/sql-createrule.html\" title=\"CREATE RULE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE RULE\u003c/span\u003e\u003c/a\u003e），但在实践中，触发器更容易理解和正确使用。另外请注意，带有规则的关系不支持 \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e注意，在视图上执行插入、更新或删除的用户，必须拥有该视图上相应的插入、更新或删除权限。另外，默认情况下，视图所有者必须在底层基关系上拥有相关权限，而执行更新的用户则不需要在底层基关系上拥有任何权限（参见\u003ca href=\"/docs/18/rules-privileges.html\" title=\"39.5. 规则和权限\"\u003e第 39.5 节\u003c/a\u003e）。但是，如果视图的 \u003ccode class=\"literal\"\u003esecurity_invoker\u003c/code\u003e设置为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e，则必须由执行更新的用户而非视图所有者，在底层基关系上拥有相关权限。\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e创建一个由所有喜剧影片组成的视图：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW comedies AS\n    SELECT *\n    FROM films\n    WHERE kind = 'Comedy';\n\u003c/pre\u003e\u003cp\u003e这会创建一个视图，其中包含创建视图时\u003ccode class=\"literal\"\u003efilms\u003c/code\u003e表中的各列。尽管创建视图时使用了\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，但以后添加到该表的列不会成为该视图的一部分。\u003c/p\u003e\u003cp\u003e创建带有\u003ccode class=\"literal\"\u003eLOCAL CHECK OPTION\u003c/code\u003e的视图：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW universal_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = 'U'\n    WITH LOCAL CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003e这会创建一个基于视图\u003ccode class=\"literal\"\u003ecomedies\u003c/code\u003e的视图，只显示 \u003ccode class=\"literal\"\u003ekind = 'Comedy'\u003c/code\u003e且 \u003ccode class=\"literal\"\u003eclassification = 'U'\u003c/code\u003e的影片。如果新行不满足 \u003ccode class=\"literal\"\u003eclassification = 'U'\u003c/code\u003e，则任何对该视图执行 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e或\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e的尝试都会被拒绝，但不会检查影片的\u003ccode class=\"literal\"\u003ekind\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e创建带有\u003ccode class=\"literal\"\u003eCASCADED CHECK OPTION\u003c/code\u003e的视图：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW pg_comedies AS\n    SELECT *\n    FROM comedies\n    WHERE classification = 'PG'\n    WITH CASCADED CHECK OPTION;\n\u003c/pre\u003e\u003cp\u003e这会创建一个同时检查新行\u003ccode class=\"literal\"\u003ekind\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eclassification\u003c/code\u003e的视图。\u003c/p\u003e\u003cp\u003e创建一个同时包含可更新列和不可更新列的视图：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE VIEW comedies AS\n    SELECT f.*,\n           country_code_to_name(f.country_code) AS country,\n           (SELECT avg(r.rating)\n            FROM user_ratings r\n            WHERE r.film_id = f.id) AS avg_rating\n    FROM films f\n    WHERE f.kind = 'Comedy';\n\u003c/pre\u003e\u003cp\u003e该视图将支持\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e。所有来自\u003ccode class=\"literal\"\u003efilms\u003c/code\u003e表的列都可更新，而计算列\u003ccode class=\"literal\"\u003ecountry\u003c/code\u003e和 \u003ccode class=\"literal\"\u003eavg_rating\u003c/code\u003e为只读。\u003c/p\u003e\u003cp\u003e创建一个由1到100的数字组成的递归视图：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE RECURSIVE VIEW public.nums_1_100 (n) AS\n    VALUES (1)\nUNION ALL\n    SELECT n+1 FROM nums_1_100 WHERE n \u0026lt; 100;\n\u003c/pre\u003e\u003cp\u003e请注意，尽管在这个\u003ccode class=\"command\"\u003eCREATE\u003c/code\u003e命令中递归视图名称带有模式限定，但其内部自引用并没有带模式限定。这是因为隐式创建的 CTE 名称不能带模式限定。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OR REPLACE VIEW\u003c/code\u003e是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的语言扩展。临时视图的概念也是如此。\u003ccode class=\"literal\"\u003eWITH ( ... )\u003c/code\u003e子句同样是扩展，安全屏障视图和安全调用者视图也是如此。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-view/?v=18\" title=\"ALTER VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-view/?v=18\" title=\"DROP VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-materialized-view/?v=18\" title=\"CREATE MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_name\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003eview_option_value\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]","synopsis_text":"CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]\n[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]\nAS query\n[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}