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

CREATE MATERIALIZED VIEW defines a query as a stored result set that is not updated each time the view is referenced.

Syntax

CREATE MATERIALIZED VIEW name
    [build_clause] [create_mv_refresh]
    AS subquery

Where build_clause is:

BUILD { IMMEDIATE | DEFERRED }

Where create_mv_refresh is:

REFRESH [COMPLETE] [ON DEMAND]

Parameter	Description
`name`	The name of the materialized view. Can be schema-qualified (for example, `myschema.dept_30`). Must be unique within the schema—no other view, table, sequence, or index can share this name.
`subquery`	A `SELECT` statement that defines the contents of the view.
`BUILD IMMEDIATE`	Populates the view immediately upon creation. Default.
`BUILD DEFERRED`	Delays population until the first `REFRESH` operation.
`COMPLETE`	On refresh, discards the current content and reloads the view by re-executing the defining query.
`ON DEMAND`	Refreshes the view when explicitly triggered by calling `DBMS_MVIEW` or the `REFRESH MATERIALIZED VIEW` statement. Default.

Materialized views are read-only. INSERT, UPDATE, and DELETE operations on a view are not allowed.
Access to the underlying tables is governed by the permissions of the view owner. The querying user must also have permissions to call all functions used by the view.
For details on the REFRESH MATERIALIZED VIEW statement, see the PostgreSQL Core Documentation.

Create a view that stores only the rows for department 30, populated immediately:

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