Database Reference
In-Depth Information
Let's start from a very simple view that might exist purely for administrative purposes, as follows:
CREATE VIEW cust_view AS
SELECT customerid
,firstname
,lastname
,age
FROM cust;
At first, if we try to INSERT into our view, we get the following error:
postgres=# INSERT INTO cust_view
postgres-# VALUES (5, 'simon', 'riggs', 133);
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
So let's try one of those as follows:
CREATE RULE cust_view_insert AS
ON insert TO cust_view
DO INSTEAD
INSERT INTO cust
VALUES (new.customerid, new.firstname, new.lastname, new.age);
And now retry our INSERT as follows:
postgres=# INSERT INTO cust_view
postgres-# VALUES (5, 'simon', 'riggs', 133);
INSERT 0 1
This now works. Let's add rules for UPDATE and DELETE also, by running the following query:
CREATE RULE cust_view_update AS
ON update TO cust_view
DO INSTEAD
UPDATE cust SET
firstname = new.firstname
,lastname = new.lastname
,age = new.age
WHERE customerid = old.customerid;
CREATE RULE cust_view_delete AS
ON delete TO cust_view
DO INSTEAD
DELETE FROM cust
WHERE customerid = old.customerid;
 
Search WWH ::




Custom Search