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;

