Create a view-PolarDB(PolarDB)-阿里云帮助中心

CREATE VIEW defines a virtual table based on a query. Use it to simplify complex queries, restrict access to underlying tables, or present data in a specific format.

Syntax

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

Parameters

Parameter	Description
`name`	The name of the view. The name can be schema-qualified (for example, `myschema.myview`).
`column_name`	An optional list of column names for the view. If omitted, column names are derived from the query.
`query`	A `SELECT` statement that defines the columns and rows of the view.

Description

CREATE VIEW defines a view of a query. Views are not physically materialized — the underlying query runs every time the view is referenced.

CREATE OR REPLACE VIEW replaces an existing view of the same name.

If a schema name is specified (for example, CREATE VIEW myschema.myview ...), the view is created in that schema. Otherwise, it is created in the current schema. The view name must not conflict with any other view, table, sequence, or index in the same schema.

Limitations

Read-only: Views do not support INSERT, UPDATE, or DELETE operations. To simulate write operations, create rules that rewrite statements on the view into appropriate actions on the underlying tables. See the Postgres Plus documentation for details on CREATE RULE.
Access permissions: Access to tables referenced in the view is determined by the permissions of the view owner. Functions called within the view require the calling user to have permissions to execute those functions directly.

Examples

Create a basic view

Filter employees in department 30:

CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30;

Create or replace a view

Update an existing view definition without dropping it first:

CREATE OR REPLACE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30 ORDER BY empno;

Create a view with explicit column names

Specify column names explicitly instead of deriving them from the query:

CREATE VIEW emp_summary (employee_id, full_name, department)
  AS SELECT empno, ename, deptno FROM emp;

Create a schema-qualified view

Create a view in a specific schema:

CREATE VIEW hr.dept_30 AS SELECT * FROM emp WHERE deptno = 30;

What's next

To modify an existing view definition, use CREATE OR REPLACE VIEW.
To remove a view, use DROP VIEW.