[GENERAL] Update rule on a view - what am I doing wrong

[Leif Jensen]

   Hello,

   I have been fighting a problem with an update rule on a view. I have a view 
that combines two tables where the 'sub' table (scont) can have several rows 
per row in the 'top' table (icont). The view combines these to show only one 
record per row in the top table. To be able to update on this view I have 
created a rule 'on update'. The rule needs to have both UPDATE, DELETE, and 
INSERT commands. Is this not possible or am I doing something else wrong ?

   In the included sql script I have tried to show the problem. When the final 
update statement is executed, I get a 'duplicate key violation' on a record 
that has never been there.

   I am using PostgreSQL version 9.1.7 (running on Linux/Ubuntu 12.04). What am 
I doing wrong ?

   Please help,

 Leif

DROP VIEW  hcont;
DROP TABLE icont;
CREATE TABLE icont (
  cid  INTEGER,
  sid  INTEGER,
  rt   INTEGER
);
ALTER TABLE icont ADD PRIMARY KEY ( cid, sid );

DROP TABLE scont;
CREATE TABLE scont (
  cid  INTEGER,
  sid  INTEGER,
  vno  INTEGER,
  val  INTEGER
);
ALTER TABLE scont ADD PRIMARY KEY ( cid, sid, vno );

CREATE VIEW hcont AS
  SELECT ic.cid AS oid, ic.cid, ic.sid, ic.rt,
         s1.val AS value1,
	 s2.val AS value2
    FROM icont ic
         LEFT OUTER JOIN scont s1 ON s1.cid = ic.cid AND s1.sid = ic.sid and s1.vno = 1
         LEFT OUTER JOIN scont s2 ON s2.cid = ic.cid AND s2.sid = ic.sid and s2.vno = 2
  ;

CREATE OR REPLACE RULE hcont_udpate AS ON UPDATE TO hcont DO INSTEAD (
  UPDATE icont SET rt = NEW.rt
    WHERE cid = NEW.cid AND sid = NEW.sid;
  DELETE FROM scont
    WHERE cid = NEW.cid AND sid = NEW.sid;
  INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 1, NEW.value1 );
  INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 2, NEW.value2 );
);

-- Populate the tables
INSERT INTO icont VALUES ( 1, 1, 20 );
INSERT INTO icont VALUES ( 1, 2, 40 );
INSERT INTO icont VALUES ( 1, 3, 60 );
INSERT INTO scont VALUES ( 1, 1, 1, 3 );
INSERT INTO scont VALUES ( 1, 1, 2, 5 );

SELECT * from scont;

UPDATE hcont SET cid = 1, sid = 2, rt = 80, value1 = 7, value2 = 9 WHERE cid = 1;


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general