Update rule on a view - what am I doing wrong

[Leif Jensen <leif@xxxxxxxxxxx>]

   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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general