From: Ferindo Middleton Jr <fmiddleton@sleekcollar.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: UPDATE/INSERT on multiple co-dependent tables
Date: Tue, 09 Nov 2004 21:26:12 -0500
Message-ID: <1100053572.9414.142.camel@scnmaster.sleekcollar.com> (raw)
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
--
www.sleekcollar.com
Ferindo Middleton, Jr.
Chief Architect
Sleekcollar Internet Application & Artistic Visualizations
ferindo.middleton@sleekcollar.com
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fmiddleton@sleekcollar.com
Subject: Re: UPDATE/INSERT on multiple co-dependent tables
In-Reply-To: <1100053572.9414.142.camel@scnmaster.sleekcollar.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