UPDATE/INSERT on multiple co-dependent tables

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: UPDATE/INSERT on multiple co-dependent tables

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: UPDATE/INSERT on multiple co-dependent tables

> 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....
Sounds like you want a foreign key with ON UPDATE CASCADE. If
that doesn't work for some reason or other you might be able
to achieve what you need with an explicit trigger on update of
registration.schedule_id.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly