pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (3+ messages)  latest in thread

Message-ID: <01C073A1.ADE7EA80.mdavis@sevainc.com>
Permalink:  ../01C073A1.ADE7EA80.mdavis@sevainc.com/
Also on:    postgresql.org/message-id/01C073A1.ADE7EA80.mdavis@sevainc.com

 · 

reply

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