Thursday, July 22, 2010

CREATE VIEW WITH CHECK OPTION in PostgreSql

CREATE VIEW working fine with PostgreSql but CREATE VIEW WITH CHECK OPTION would show error message like WITH CHECK OPTION is not implemented in postgres

In PostgreSql we don't have "create view .. with check option", we could do the same in alternative way by creating rule.

create rule

I) for insert

CREATE OR REPLACE RULE rule_name AS ON INSERT TO view_name
DO INSTEAD
INSERT INTO table_name VALUES(id=new.id,name=new.name)

II) for Update

CREATE OR REPLACE RULE rule_name AS ON UPDATE TO view_name
DO INSTEAD
UPDATE table_name SET name=new.name WHERE id=new.id

III) for Delete

CREATE OR REPLACE RULE rule_name AS ON DELETE TO view_name
DO INSTEAD
DELETE FROM table_name WHERE id=new.id

example

you are having table 'students ' with 'id' and 'name' column

create view vw_students as select * from students

create rule for up-datable view

CREATE OR REPLACE RULE update_vw AS ON UPDATE TO vw_students
DO INSTEAD
UPDATE students SET name=new.name WHERE id=new.id

now you can able update the view by

update vw_students set name='postgres' where id=1

note : parent table also get updated whenever you update on view
Post a Comment