pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Updating two table via a Rule?
3+ messages / 2 participants
[nested] [flat]

* Updating two table via a Rule?
@ 2001-01-01 10:19  Michael Davis <mdavis@sevainc.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Davis @ 2001-01-01 10:19 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Updating two table via a Rule?
@ 2001-01-01 10:41  Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael Davis @ 2001-01-01 10:41 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Updating two table via a Rule?
@ 2001-01-01 19:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2001-01-01 19:21 UTC (permalink / raw)
  To: mdavis@sevainc.com <mdavis@sevainc.com>; +Cc: pgsql-sql

Try doing the two inserts in one rule:

CREATE RULE ... DO INSTEAD
(
INSERT INTO ... ;
INSERT INTO ... ;
);

This is the only way of controlling the order in which the actions will
be done; separate rules will be applied in an undefined order.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-01-01 19:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-01 10:19 Updating two table via a Rule? Michael Davis <mdavis@sevainc.com>
2001-01-01 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-01 10:41 RE: Updating two table via a Rule? Michael Davis <mdavis@sevainc.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox