Materialized views names are defined by:
view_name ::= re('[a-zA-Z_0-9]+')
You can create a materialized view on a table using a
CREATE MATERIALIZED VIEW statement:
create_materialized_view_statement ::= CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]
select_statementPRIMARY KEY '('
CREATE MATERIALIZED VIEW monkeySpecies_by_population AS SELECT * FROM monkeySpecies WHERE population IS NOT NULL AND species IS NOT NULL PRIMARY KEY (population, species) WITH comment='Allow query by population instead of species';
CREATE MATERIALIZED VIEW statement creates a new materialized view. Each such view is a set of rows which
corresponds to rows which are present in the underlying, or base, table specified in the
SELECT statement. A
materialized view cannot be directly updated, but updates to the base table will cause corresponding updates in the
Creating a materialized view has 3 main parts:
Attempting to create an already existing materialized view will return an error unless the
IF NOT EXISTS option is
used. If it is used, the statement will be a no-op if the materialized view already exists.
The select statement of a materialized view creation defines which of the base table is included in the view. That statement is limited in a number of ways:
SELECT *isn’t allowed if the base table has static columns).
WHEREclause have the following restrictions:
IS NOT NULLrestriction. No other restriction is allowed.
IS NOT NULLrestriction (or any other restriction, but they must have one).
A view must have a primary key and that primary key must conform to the following restrictions:
So for instance, give the following base table definition:
CREATE TABLE t ( k int, c1 int, c2 int, v1 int, v2 int, PRIMARY KEY (k, c1, c2) )
then the following view definitions are allowed:
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (c1, k, c2) CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (v1, k, c1, c2)
but the following ones are not allowed:
// Error: cannot include both v1 and v2 in the primary key as both are not in the base table primary key CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL AND v1 IS NOT NULL PRIMARY KEY (v1, v2, k, c1, c2) // Error: must include k in the primary as it's a base table primary key column CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (c1, c2)
After creation, you can alter the options of a materialized view using the
ALTER MATERIALIZED VIEW statement:
alter_materialized_view_statement ::= ALTER MATERIALIZED VIEW
The options that can be updated are the same than at creation time and thus the same than for tables.
Dropping a materialized view users the
DROP MATERIALIZED VIEW statement:
drop_materialized_view_statement ::= DROP MATERIALIZED VIEW [ IF EXISTS ]
If the materialized view does not exists, the statement will return an error, unless
IF EXISTS is used in which case
the operation is a no-op.