- Simple Examples of Views
- Modifying the Content of Views
- Conclusion
This chapter is from the book
This chapter is from the book
This chapter is from the book
Modifying the Content of Views
Modifying the content of a view is one of the most important things when dealing with views. Because the database is not able to find out what has to be done when inserting in a view, the user has to define rules. In this section, you will learn how rules can be defined and how they can be used efficiently.
In order to insert data, you have to create a rule like the one shown below:
CREATE RULE view_data_insert AS ON INSERT TO view_data
DO INSTEAD
INSERT INTO data VALUES(
(NEW.c/2)::int4,
(NEW.d/2)::int4);
You can see that a record has to be inserted into table "data" because the view does not contain the actual information. Because the view multiplies all values in "data" with two, it is necessary to divide the values someone wants to insert into the view by two. The problem is that "data" contains integer values, so it may be a problem to perform the division. Therefore, we cast the result to integer so that the value can be inserted into the table. Side effects that occur because of operations such as casting have to be taken into consideration.
The rule for performing update operations is similar to the one we have defined for insert operations:
CREATE RULE view_data_update AS ON UPDATE TO view_data
DO INSTEAD
UPDATE data SET
a=(NEW.c/2)::int4,
b=(NEW.d/2)::int4;
We have to update "data" instead of updating "view_data". The same applies to the rule for deleting records—the records have to be deleted in "data" and not in "view_data".
CREATE RULE view_data_delete AS ON DELETE TO view_data
DO INSTEAD
DELETE FROM data
WHERE a=(OLD.c/2)::int4
AND b=(OLD.d/2)::int4;
After defining all rules, we can try performing simple SQL operations.
Here is an example of an INSERT statement:
view=# INSERT INTO view_data VALUES(20, 40); INSERT 387848 1
Below you can see that the data has been inserted into "data".
view=# SELECT * FROM data; a | b ----+---- 12 | 23 10 | 20 (2 rows)
Update operations can be performed the same way:
view=# UPDATE view_data SET c=18, d=42; UPDATE 2
In our example, we update all values in the table:
view=# SELECT * FROM data; a | b ---+---- 9 | 21 9 | 21 (2 rows)
Deleting records is performed by the rule "view_data_delete". The example below shows how the rule handles DELETE operations:
view=# DELETE FROM view_data; DELETE 2 view=# SELECT * FROM data; a | b ---+--- (0 rows)