Updateable Views in PostgreSQL 9.1 using INSTEAD OF Trigger

28 Oct

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 tidious, 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.

About these ads

10 Responses to “Updateable Views in PostgreSQL 9.1 using INSTEAD OF Trigger”

  1. CraigTorson November 4, 2011 at 03:45 #

    Cool information as for me. Thanks a lot for providing that info. By the way, why don’t you get another header?
    Well, at least to something like this jamming device website has.

  2. Magnus Rolf September 4, 2012 at 07:30 #

    Great post: straight forward explaination how updateable views works in PostgreSQL.
    love it!

  3. Radovan Jablonovsky September 21, 2012 at 19:19 #

    What is the performance side of this implementation?

    • Database Technologies September 21, 2012 at 19:23 #

      I don’t thing there will be any performance overhead. Its just trigger which are redirecting the DML to base table.

  4. Thomas Dunz November 12, 2012 at 21:21 #

    Helped me a lot figuring out how POSTGRES and QGIS may work together when usind more complex datastructures.

    • Agustin May 27, 2013 at 18:39 #

      The trigger enabled me to edit a Postgres view within QGIS.

  5. Krishna December 19, 2012 at 18:48 #

    Gun shot response, crystal clear explanation
    Thanks

  6. proozack January 20, 2013 at 21:57 #

    super; It’s very simple now. Thanks

Trackbacks/Pingbacks

  1. Database Technologies - December 16, 2011

    [...] Features 2. New Replication and Recovery Features 3. Queries improvement of Core PostgreSQL 9.1 4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1 5. Utility Operation Improvements 6. Upsert/Merge using Writeable CTE 7. New Functions/Improvement [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: