RE: Updating two table via a Rule? - Mailing list pgsql-sql

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@sevainc.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