SQL: The PostgreSQL Way - PostgreSQL: Up and Running - page 123

If we try to update a field not in our update row comparison, as shown here, the update
will not take place:
UPDATE census . vw_facts SET short_name = 'test' ;
The output message would be:
Query returned successfully: 0 rows affected, 931 ms execution time.
Although this example created a single trigger function to handle multiple events, we
could have just as easily created a separate trigger and trigger function for each event.
Materialized Views
Materialized views cache the data fetched. This happens when you first create the view
as well as when you run the REFRESH MATERIALIZED VIEW command. To use material‐
ized views, you need at least version 9.3.
The most convincing cases for using materialized views are when the underlying query
takes a long time and when having timely data is not critical. You encounter these sce‐
narios when building online analytical processing (OLAP) applications.
Unlike with nonmaterialized views, you can add indexes to materialized views to speed
up the read.
Example 7-6 demonstrates how to make a materialized view version of Example 7-1 .
Example 7-6. Materialized view
CREATE MATERIALIZED VIEW census . vw_facts_2011_materialized AS
SELECT fact_type_id , val , yr , tract_id FROM census . facts WHERE yr = 2011 ;
Create an index on a materialized view as you would do on a regular table, as shown in
Example 7-7 .
Example 7-7. Add index to materialized view
CREATE UNIQUE INDEX ix
ON census . vw_facts_2011_materialized ( tract_id , fact_type_id , yr );
For speedier access to a materialized view with a large number of records, you may want
to control the physical sort of the data. The easiest way is to include an ORDER BY when
you create the view. Alternatively, you can add a cluster index to the view. First create
an index in the physical sort order you want to have. Then run the CLUSTER command,
passing it the index, as shown in Example 7-8 .
Example 7-8. Clustering a view on an index
CLUSTER census . vw_facts_2011_materialized USING ix ;
CLUSTER census . vw_facts_2011_materialized ;