CREATE MATERIALIZED VIEW
Defines a new materialized view.
Synopsis
CREATE MATERIALIZED VIEW <table_name>
[ (<column_name> [, ...] ) ]
[ WITH ( <storage_parameter> [= <value>] [, ... ] ) ]
[ TABLESPACE <tablespace_name> ]
AS <query>
[ WITH [ NO ] DATA ]
[DISTRIBUTED {| BY (<column> [<opclass>], [ ... ]) | RANDOMLY | REPLICATED }]Description
CREATE MATERIALIZED VIEW defines a materialized view of a query.
The query is run and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and can be refreshed using REFRESH MATERIALIZED VIEW.
CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand.
To refresh materialized view data, use the REFRESH MATERIALIZED VIEW command.
A materialized view has many of the same properties as a table, but there is no support for temporary materialized views or automatic generation of OIDs.
Parameters
| Parameter | Description |
|---|---|
table_name |
The name (optionally schema-qualified) of the materialized view to be created |
column_name |
The name of a column in the materialized view. The column names are assigned based on position. The first column name is assigned to the first column of the query result, and so on. If a column name is not provided, it is taken from the output column names of the query |
WITH ( <storage_parameter> [= <value>] [, … ] ) |
This clause specifies optional storage parameters for the materialized view.
All parameters supported for |
TABLESPACE <tablespace_name> |
|
query |
A SELECT or VALUES command. This query will run within a security-restricted operation. In particular, calls to functions that themselves create temporary tables will fail |
WITH [ NO ] DATA |
This clause specifies whether or not the materialized view should be populated with data at creation time.
|
DISTRIBUTED BY (<column> [<opclass>], [ … ] ) |
Used to declare the Greengage DB distribution policy for the materialized view data. For information about a table distribution policy, see CREATE TABLE |
DISTRIBUTED RANDOMLY |
Used to declare the Greengage DB distribution policy for the materialized view data. For information about a table distribution policy, see CREATE TABLE |
DISTRIBUTED REPLICATED |
Used to declare the Greengage DB distribution policy for the materialized view data. For information about a table distribution policy, see CREATE TABLE |
Notes
Materialized views are read-only.
The system will not allow an INSERT, UPDATE, or DELETE command on a materialized view.
Use REFRESH MATERIALIZED VIEW to update the materialized view data.
If you want the data to be ordered upon generation, you must use an ORDER BY clause in the materialized view query.
However, if a materialized view query contains an ORDER BY or SORT clause, the data is not guaranteed to be ordered or sorted if SELECT is performed on the materialized view.
Examples
Create a view consisting of all comedy films:
CREATE MATERIALIZED VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'comedy';This will create a view containing the columns that are in the film table at the time of view creation.
Though * was used to create the materialized view, columns added later to the table will not be part of the view.
Create a view that gets the top ten ranked baby names:
CREATE MATERIALIZED VIEW topten AS
SELECT name, rank, gender, year
FROM names,
rank
WHERE rank < '11'
AND names.id = rank.id;Compatibility
CREATE MATERIALIZED VIEW is a Greengage DB extension of the SQL standard.