Hello.
This section works fine:
CREATE TABLE torder (
id INT8 NOT NULL PRIMARY KEY
);
CREATE TABLE torder_row (
id INT8 NOT NULL PRIMARY KEY,
torder_id INT8 NOT NULL REFERENCES torder (id) ON DELETE RESTRICT
);
CREATE VIEW vorder(orderID, rowID) AS SELECT 1, 1 FROM torder;
CREATE RULE vorder_ins AS ON INSERT TO vorder
DO INSTEAD
(INSERT INTO torder VALUES (NEW.orderID);
INSERT INTO torder_row VALUES (NEW.rowID, NEW.orderID);
);
CREATE RULE vorder_upd AS ON UPDATE TO vorder
DO INSTEAD
(INSERT INTO torder (id) VALUES (NEW.orderID);
UPDATE torder_row SET torder_id = NEW.orderID;
DELETE FROM torder WHERE id = OLD.orderID;
);
INSERT INTO vorder VALUES (1, 1);
UPDATE vorder SET orderID = 2;
SELECT * FROM vorder;
But when I change the view to the following:
CREATE VIEW vorder(orderID, rowID) AS SELECT 1, 1 FROM torder;
becomes
CREATE VIEW vorder(orderID, rowID) AS SELECT id, 1 FROM torder;
I get the following error:
ERROR: insert or update on table "torder_row " violates foreign key
constraint "$1"
This was just a small test so the table definitions and view makes
little sense, but generally, any time the view actualy selects columns
it seems the update rule fails with a constraint violation even though
it shouldn't.
Thank's for reading!
/Peter
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
