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

Version 9.3 also introduced materialized views . When you mark a view as materialized,
it will requery the data only when you issue the REFRESH command. The upside is that
you're not wasting resources running complex queries repeatedly; the downside is that
you might not have the most up-to-date data when you use the view.
Version 9.4 allows users to access materialized views while it refreshes. It also introduced
the WITH CHECK OPTION modifier, which prevents inserts and updates outside the scope
of the view.
Single Table Views
The simplest view draws from a single table. Always include the primary key if you
intend to write data back to the table, as shown in Example 7-1 .
Example 7-1. Single table view
CREATE OR REPLACE VIEW census . vw_facts_2011 AS
SELECT fact_type_id , val , yr , tract_id FROM census . facts WHERE yr = 2011 ;
As of version 9.3, you can alter the data in this view by using an INSERT , UPDATE , or
DELETE command. Updates and deletes will abide by any WHERE condition you have as
part of your view. For example, the following delete will delete only records whose yr
is 2011:
DELETE FROM census . vw_facts_2011 WHERE val = 0 ;
And the following will not update any records:
UPDATE census . vw_facts_2011 SET val = 1 WHERE val = 0 AND yr = 2012 ;
Be aware that you can insert and update data that places it outside of the view's WHERE
condition:
UPDATE census . vw_facts_2011 SET yr = 2012 WHERE yr = 2011 ;
The update does not violate the WHERE condition. But once it's executed, you would have
emptied your view. For the sake of sanity, you may find it desirable to prevent updates
or inserts that could put records outside of the scope of the WHERE . Version 9.4 introduced
the WITH CHECK OPTION to accomplish this. Include this modifier when creating the
view and PostgreSQL will forever balk at any attempts to add records outside the view
and to update records that will put them outside the view. In our example view, our goal
is to limit the vw_facts_2011 to allow inserts only of 2011 data and disallow updates of
the yr to something other than 2011. To add this restriction, we revise our view defi‐
nition as shown in Example 7-2 .
Example 7-2. Single table view WITH CHECK OPTION
CREATE OR REPLACE VIEW census . vw_facts_2011 AS
SELECT fact_type_id , val , yr , tract_id
FROM census . facts WHERE yr = 2011 WITH CHECK OPTION ;