X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 10FD53A4076
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 02:26:17 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 59019-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 02:26:12 +0000 (GMT)
Received: from scnmaster.sleekcollar.com
	(pool-151-200-47-157.res.east.verizon.net [151.200.47.157])
	by svr1.postgresql.org (Postfix) with ESMTP id 4D7383A405C
	for <pgsql-sql@postgresql.org>; Wed, 10 Nov 2004 02:26:14 +0000 (GMT)
Received: from scnmaster.sleekcollar.com (scnmaster.sleekcollar.com
	[127.0.0.1])
	by scnmaster.sleekcollar.com (8.12.11/8.12.10) with ESMTP id
	iAA2QEji009924
	for <pgsql-sql@postgresql.org>; Tue, 9 Nov 2004 21:26:14 -0500
Received: (from s068913@localhost)
	by scnmaster.sleekcollar.com (8.12.11/8.12.11/Submit) id iAA2QDkt009923
	for pgsql-sql@postgresql.org; Tue, 9 Nov 2004 21:26:13 -0500
X-Authentication-Warning: scnmaster.sleekcollar.com: s068913 set sender to
	fmiddleton@sleekcollar.com using -f
Subject: UPDATE/INSERT on multiple co-dependent tables
From: Ferindo Middleton Jr <fmiddleton@sleekcollar.com>
Reply-To: fmiddleton@sleekcollar.com
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary="=-W02MDxbJEjay9PDJ8FBT"
Organization: Sleek Collar
Message-Id: <1100053572.9414.142.camel@scnmaster.sleekcollar.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Tue, 09 Nov 2004 21:26:12 -0500
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE,
	RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL
X-Spam-Level: *
X-Archive-Number: 200411/133
X-Sequence-Number: 19452


--=-W02MDxbJEjay9PDJ8FBT
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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

--=-W02MDxbJEjay9PDJ8FBT
Content-Type: text/html; charset=utf-8

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.0.10">
</HEAD>
<BODY>
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: <BR>
<BR>
Example: I have two tables: registration &amp; schedules.... <BR>
they both record a class_id, start_date,&nbsp; end_date... I want to make sure that if the schedule_id field is updated in the registration table; that class_id, start_date &amp; 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:<BR>
<BR>
CREATE RULE registration_update AS<BR>
ON UPDATE TO registration<BR>
DO<BR>
	UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id);<BR>
<BR>
What I'm doing is kind of redundant but necessary for backwards-compatibility<BR>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
-- <BR>
www.sleekcollar.com<BR>
Ferindo Middleton, Jr.<BR>
Chief Architect<BR>
Sleekcollar Internet Application &amp; Artistic Visualizations<BR>
<A HREF="mailto:ferindo.middleton@sleekcollar.com"><U>ferindo.middleton@sleekcollar.com</U></A>
</TD>
</TR>
</TABLE>

</BODY>
</HTML>

--=-W02MDxbJEjay9PDJ8FBT--