Date: Tue, 9 Nov 2004 19:19:37 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Ferindo Middleton, Jr" <fmiddleton@verizon.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE/INSERT on multiple co-dependent tables
In-Reply-To: <000c01c4c6ce$03da7e70$1001a8c0@RENNYSLAPTOP>
Message-ID: <20041109191724.F63596@megazone.bigpanda.com>
References: <000c01c4c6ce$03da7e70$1001a8c0@RENNYSLAPTOP>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 9 Nov 2004, Ferindo Middleton, Jr wrote:

> Is it possible for an UPDATE/INSERT query string to function in such a way
> that it requires two like fields in different tables to be equal to/'in sync
> with' one another:
>
> Example: I have two tables: registration & schedules....
> they both record a class_id, start_date, end_date... I want to make sure
> that if the schedule_id field is updated in the registration table; that
> class_id, start_date & end_date fields automatically change to match the
> schedules.id record in the schedules table.... I've devised a function to
> handle this but pgsql recognizes the query to be 'infinitely recursive:
>
> CREATE RULE registration_update AS
> ON UPDATE TO registration
> DO
> UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id
> = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id);
>
> What I'm doing is kind of redundant but necessary for
> backwards-compatibility

You would probably have better luck doing something like the above in a
before trigger rather than a rule by having the before trigger change
NEW.class_id to the desired value.