Database Reference
In-Depth Information
We emulate the view by first creating a select rule on the dummy table, and then try to create
triggers on the table for the
INSERT
,
UPDATE
, and
DELETE
actions. The rule only works if the
table is completely empty, and if the rule is named
_RETURN
.
postgres # CREATE RULE "_RETURN" AS
ON SELECT TO cust_view
DO INSTEAD
SELECT * FROM cust;
CREATE RULE
postgres # CREATE TRIGGER cust_view_modify_after_trig
AFTER INSERT OR UPDATE OR DELETE ON cust
FOR EACH ROW
EXECUTE PROCEDURE cust_view_modify_trig_proc();
ERROR: "cust_view" is not a table
Huh? So what is it if it's not a table?
postgres # DROP TABLE cust_view;
ERROR: "cust_view" is not a table
HINT: Use DROP VIEW to remove a view
postgres # DROP VIEW cust_view;
DROP VIEW
Wow! That works! Yes, we created a table, then added a rule to it, and it
turned
the
table
into
a
view
. So, now we realize that we can't put triggers on a view, and we can't put a
SELECT
rule
on a table without it becoming a view. So, this route won't work at all. It is probably best just
to accept that if you want to load data into a table, then you have to refer to the table directly,
rather than use a view. PostgreSQL 9.1 will support INSTEAD OF triggers on views, providing a
full solution to updateable views that follows the SQL Standard.
