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

Now try to run an update such as:
UPDATE census . vw_facts_2011 SET yr = 2012 WHERE val > 2942 ;
You'll get an error:
ERROR : new row violates WITH CHECK OPTION for view "vw_facts_2011"
DETAIL : Failing row contains ( 1 , 25001010500 , 2012 , 2985 . 000 , 100 . 00 ).
Using Triggers to Update Views
Views encapsulate joins among tables. When a view draws from more than one table,
updating the underlying data with a simple command is no longer possible. Having
more than one table introduces an inherent ambiguity when you're trying to change the
underlying data, and PostgreSQL is not about to make an arbitrary decision for you.
For instance, if you have a view that joins a table of countries with a table of provinces,
and then decide to delete one of the rows, PostgreSQL won't know whether you intend
to delete only a country, a province, or a particular country-province pairing. None‐
theless, you can still modify the underlying data through the view—using triggers.
Let's start by creating a view pulling from the facts table and a lookup table, as shown
in Example 7-3 .
Example 7-3. Creating view vw_facts
CREATE OR REPLACE VIEW census . vw_facts AS
SELECT y . fact_type_id , y . category , y . fact_subcats , y . short_name , x . tract_id , x . yr ,
x . val , x . perc
FROM census . facts As x INNER JOIN census . lu_fact_types As y
ON x . fact_type_id = y . fact_type_id ;
To make this view updatable with a trigger, you can define one or more INSTEAD OF
triggers. We first define the trigger function to handle the trifecta: INSERT , UPDATE ,
DELETE . You can use any language to write the function, and you're free to name it
whatever you like. We chose PL/pgSQL in Example 7-4 .
Example 7-4. Trigger function for vw_facts to insert, update, delete
CREATE OR REPLACE FUNCTION census . trig_vw_facts_ins_upd_del () RETURNS trigger AS
$$
BEGIN
IF ( TG_OP = 'DELETE' ) THEN
DELETE FROM census . facts AS f
WHERE
f . tract_id = OLD . tract_id AND f . yr = OLD . yr AND
f . fact_type_id = OLD . fact_type_id ;
RETURN OLD ;
END IF ;
IF ( TG_OP = 'INSERT' ) THEN
INSERT INTO census . facts ( tract_id , yr , fact_type_id , val , perc )
SELECT NEW . tract_id , NEW . yr , NEW . fact_type_id , NEW . val , NEW . perc ;