Database Reference
In-Depth Information
Rules versus triggers - creating updateable
views with rules
In the various sections referring to triggers, we implemented updateable views
using triggers; let's do it using rules. We are lucky that in the latest versions of
PostgreSQL, we have auto-updateable views. We will use the same example
where we implemented updateable views using triggers for the INSERT case.
Drop the tab_view table using the following statement:
warehouse_db=# DROP TABLE tab_view CASCADE;
Create the tab_view table again in the following manner:
warehouse_db=# CREATE TABLE tab_view
(
emp_id INT NOT NULL,
emp_name VARCHAR(10),
emp_city VARCHAR(10)
);
Let's insert a few rows in the table in the following manner:
warehouse_db=# INSERT INTO tab_view VALUES (1, 'Adam', 'Chicago');
warehouse_db=# INSERT INTO tab_view VALUES (2, 'John', 'Miami');
warehouse_db=# INSERT INTO tab_view VALUES (3, 'Smith', 'Dallas');
Create the view_select view using the following statement:
warehouse_db=# CREATE VIEW view_select AS SELECT * FROM tab_view;
Create the rule for INSERT on the view in the following manner:
warehouse_db=# CREATE RULE view_select_insert AS ON INSERT
TO view_select
DO INSTEAD (INSERT INTO tab_view VALUES
(NEW.emp_id, NEW.emp_name, NEW.emp_city));
You can list your rule by querying the pg_rewrite catalog table to see whether your
rule has been created in the following manner:
warehouse_db=# SELECT rulename FROM pg_rewrite WHERE
rulename='view_select_insert';
rulename
--------------------
view_select_insert
(1 row)
 
Search WWH ::




Custom Search