How do I create a rule for a view that inserts into two tables?
I have a view based on two tables. I would like to create insert, update,
and delete rules for this view to:
- update both tables when the view is updated
- delete from both tables when a record is deleted from the view
- insert into both table when a record is inserted into the view
Here is the view:
CREATE VIEW reg_PaymentLines AS
SELECT P.MemberID, P.PaymentsID, P.PaymentDate,
PL.PaymentLineID, PL.PaymentTypesID, PL.Amount
FROM Payments P, PaymentLines PL
WHERE P.PaymentsID = PL.PaymentsID;
I have tried to create two insert rules on the view as follows:
CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines
DO INSTEAD
INSERT INTO PaymentLines (PaymentsID, PaymentLineID, Amount)
VALUES (new.PaymentsID, new.PaymentLineID,
new.Amount);
CREATE RULE reg_PaymentLines_r2 AS ON INSERT TO reg_PaymentLines
DO INSTEAD
INSERT INTO Payments (MemberID, PaymentsID, PaymentDate,
Amount)
VALUES (new.MemberID, new.PaymentsID, new.PaymentDate,
new.Amount);
PostgreSQL allows me to create the two rules. However, when I insert into
the view, I get a foreign key constraint violation because the PaymentID
does not exist in the Payments table. There is a foreign key constraint
from PaymentLines.PaymentsID to Payments.PaymentsID. It appears that
either:
- the insert into the PaymentLines table before the insert occurs in the
Payments tables
- or that the insert into the PaymentLines table is not aware of the insert
into the Payments table.
- or that the insert to the Payments table is being ignored
I get the same error regardless of how the two rules are created (i.e. same
error if rule #2 is applied before rule #1).
Any suggestions on how to get this to work? Any help is greatly
appreciated.
Thanks, Michael Davis
