From: Michael Davis <mdavis@sevainc.com>
To: PostgreSQL-SQL <pgsql-sql@postgresql.org>
Subject: Updating two table via a Rule?
Date: Mon, 1 Jan 2001 03:19:37 -0700
Message-ID: <01C073A1.ADE7EA80.mdavis@sevainc.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mdavis@sevainc.com
Subject: Re: Updating two table via a Rule?
In-Reply-To: <01C073A1.ADE7EA80.mdavis@sevainc.com>
References: <01C073A1.ADE7EA80.mdavis@sevainc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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