Posts

Showing posts with the label Update-able View.

Updateable Views in PostgreSQL 9.1 using INSTEAD OF Trigger

About updateable views user ask many times. Is it supported in PostgreSQL? Can we write Complex updateable views? Answer for above is yes . Till 9.0, we have to use RULE for implementing updateable view. Again, RULE Implementation used to be a bit work, since user has to write multiple RULES to implement this feature. Following code can be use to see this. CREATE TABLE person_detail(pid NUMERIC PRIMARY KEY, pname TEXT); CREATE TABLE person_job(pid NUMERIC PRIMARY KEY references person_detail(pid), job TEXT); INSERT INTO person_detail VALUES(1,'Angela'); INSERT INTO person_detail VALUES(2,'Tom'); INSERT INTO person_detail VALUES(3,'Heikki'); INSERT INTO person_job VALUES(1,'Documenter'); INSERT INTO person_job VALUES(2,'Developer'); INSERT INTO person_job VALUES(3,'Commiter'); CREATE VIEW person_detail_job_vw AS SELECT p.pid, p.pname, j.job FROM person_detail p LEFT JOIN person_job j ON (j.pid=p.pid); SELECT * FROM person_detail