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_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter

Till 9.0, User has to write rules something like given below:

  1. INSERT RULE

CREATE RULE person_detaik_job_vw_INSERT AS ON INSERT TO person_detail_job_vw DO INSTEAD (
       INSERT INTO  person_detail VALUES(NEW.pid,NEW.pname);
       INSERT INTO  person_job VALUES(NEW.pid,NEW.job)
      );

2. UPDATE RULE:

CREATE RULE person_detaik_job_vw_UPDATE AS ON UPDATE TO person_detail_job_vw DO INSTEAD (
       UPDATE person_detail SET pid=NEW.pid, pname=NEW.pname WHERE pid=OLD.pid;
       UPDATE person_job SET pid=NEW.pid, job=NEW.job WHERE pid=OLD.pid
      );

3. DELETE RULE:

  CREATE OR REPLACE RULE person_detaik_job_vw_DELETE AS ON DELETE TO person_detail_job_vw DO INSTEAD (
       DELETE FROM person_job WHERE pid=OLD.pid;
       DELETE FROM person_detail WHERE pid=OLD.pid
      );

Lets see RULE WORK:

 INSERT INTO person_detail_job_vw VALUES(4,'Singh','New JOB');
 INSERT 0 1

 SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | New JOB
(4 rows)


 UPDATE person_detail_job_vw SET job='PATCHER'  WHERE pid=4;
 UPDATE 1

  SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | PATCHER

 DELETE FROM person_detail_job_vw WHERE pid=4;
 DELETE 1

 SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter

If view has more complex query, then I need to break into more RULES.

Now in PostgreSQL 9.1, user can use INSTEAD OF Trigger. Following is an example:


Trigger Function

CREATE OR REPLACE FUNCTION person_detail_job_vw_dml() 
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   BEGIN 
      IF TG_OP = 'INSERT' THEN
        INSERT INTO  person_detail VALUES(NEW.pid,NEW.pname);
        INSERT INTO  person_job VALUES(NEW.pid,NEW.job);
        RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
       UPDATE person_detail SET pid=NEW.pid, pname=NEW.pname WHERE pid=OLD.pid;
       UPDATE person_job SET pid=NEW.pid, job=NEW.job WHERE pid=OLD.pid;
       RETURN NEW;
      ELSIF TG_OP = 'DELETE' THEN
       DELETE FROM person_job WHERE pid=OLD.pid;
       DELETE FROM person_detail WHERE pid=OLD.pid;
       RETURN NULL;
      END IF;
      RETURN NEW;
    END;
$function$;

Trigger:

    
CREATE TRIGGER person_detail_job_vw_dml_trig 
    INSTEAD OF INSERT OR UPDATE OR DELETE ON
      person_detail_job_vw FOR EACH ROW EXECUTE PROCEDURE person_detail_job_vw_dml();         

Lets see its work:

postgres=# SELECT VERSION();
                                                            version                                                             
--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.0 on x86_64-apple-darwin, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit

postgres=# CREATE TRIGGER person_detail_job_vw_dml_trig 
postgres-#     INSTEAD OF INSERT OR UPDATE OR DELETE ON
postgres-#       person_detail_job_vw FOR EACH ROW EXECUTE PROCEDURE person_detail_job_vw_dml();  
CREATE TRIGGER
postgres=# INSERT INTO person_detail_job_vw VALUES(4,'Singh','New JOB');
INSERT 0 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | New JOB
(4 rows)

postgres=#  UPDATE person_detail_job_vw SET job='PATCHER'  WHERE pid=4;
UPDATE 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | PATCHER
(4 rows)
postgres=# DELETE FROM person_detail_job_vw WHERE pid=4;
DELETE 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job     
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
(3 rows)

Wow! Now, in 9.1, if somebody has to do any implementation, they can also do using plpgsql function.