RE: Updating two table via a Rule?

I just answered my question. For anyone how may be interested, here is the
answer. The following create rule allows multiple actions. This was not
very clear from the documentation. Would some be willing to suggest to the
documentation group to add an example of a rule with multiple actions? I
stumbled onto this syntax in an email on the hacker list after several
hours of research.

CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines
DO INSTEAD (
INSERT INTO Payments (MemberID, PaymentsID, PaymentDate,
Amount)
VALUES (new.MemberID, new.PaymentsID, new.PaymentDate,
new.Amount);
INSERT INTO PaymentLines (MemberID, PaymentsID, PaymentLineID,
Amount)
VALUES (new.MemberID, new.PaymentsID,
new.PaymentLineID, new.Amount);
);

Here is an insert that works:

insert into reg_PaymentLines (MemberID, PaymentsID, PaymentDate,
PaymentLineID, Amount)
VALUES(99999, 777777,
'1/1/2001', nextval('PaymentLines_s'), 10);

Here is the insert that is failing:

insert into reg_PaymentLines (MemberID, PaymentsID, PaymentDate,
PaymentLineID, Amount)
VALUES(99999, nextval('Payments_s'), '1/1/2001',
nextval('PaymentLines_s'), 10);

The Payments_s sequences is bumped on both inserts. As a result, the
insert into the PaymentLines table has a different PaymentsID that the
insert into the Payments table.

Thanks, Michael Davis

-----Original Message-----
From: Michael Davis [SMTP:mdavis(at)sevainc(dot)com]
Sent: Monday, January 01, 2001 3:20 AM
To: PostgreSQL-SQL
Subject: Updating two table via a Rule?

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