pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE/INSERT on multiple co-dependent tables
4+ messages / 4 participants
[nested] [flat]
* UPDATE/INSERT on multiple co-dependent tables
@ 2004-11-10 02:26 Ferindo Middleton Jr <fmiddleton@sleekcollar.com>
2004-11-14 08:47 ` Re: UPDATE/INSERT on multiple co-dependent tables Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Ferindo Middleton Jr @ 2004-11-10 02:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE/INSERT on multiple co-dependent tables
2004-11-10 02:26 UPDATE/INSERT on multiple co-dependent tables Ferindo Middleton Jr <fmiddleton@sleekcollar.com>
@ 2004-11-14 08:47 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Karsten Hilbert @ 2004-11-14 08:47 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* UPDATE/INSERT on multiple co-dependent tables
@ 2004-11-10 02:35 Ferindo Middleton, Jr <fmiddleton@verizon.net>
2004-11-10 03:19 ` Re: UPDATE/INSERT on multiple co-dependent tables Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ferindo Middleton, Jr @ 2004-11-10 02:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPDATE/INSERT on multiple co-dependent tables
2004-11-10 02:35 UPDATE/INSERT on multiple co-dependent tables Ferindo Middleton, Jr <fmiddleton@verizon.net>
@ 2004-11-10 03:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-11-10 03:19 UTC (permalink / raw)
To: Ferindo Middleton, Jr <fmiddleton@verizon.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-11-14 08:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-10 02:26 UPDATE/INSERT on multiple co-dependent tables Ferindo Middleton Jr <fmiddleton@sleekcollar.com>
2004-11-14 08:47 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2004-11-10 02:35 UPDATE/INSERT on multiple co-dependent tables Ferindo Middleton, Jr <fmiddleton@verizon.net>
2004-11-10 03:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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