From fmiddleton@sleekcollar.com Wed Nov  10 02:26:17 2004
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--



From fmiddleton@verizon.net Wed Nov  10 02:36:00 2004
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 AB8143A409E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 02:36:00 +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 63074-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 02:35:53 +0000 (GMT)
Received: from out007.verizon.net (out007pub.verizon.net [206.46.170.107])
	by svr1.postgresql.org (Postfix) with ESMTP id B6D5F3A409A
	for <pgsql-sql@postgresql.org>; Wed, 10 Nov 2004 02:35:56 +0000 (GMT)
Received: from RENNYSLAPTOP ([151.200.47.157]) by out007.verizon.net
	(InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP
	id <20041110023556.MQUG22257.out007.verizon.net@RENNYSLAPTOP>
	for <pgsql-sql@postgresql.org>; Tue, 9 Nov 2004 20:35:56 -0600
Reply-To: <fmiddleton@verizon.net>
From: "Ferindo Middleton, Jr" <fmiddleton@verizon.net>
To: <pgsql-sql@postgresql.org>
Subject: UPDATE/INSERT on multiple co-dependent tables
Date: Tue, 9 Nov 2004 21:35:58 -0500
Message-ID: <000c01c4c6ce$03da7e70$1001a8c0@RENNYSLAPTOP>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2742.200
Importance: Normal
X-Authentication-Info: Submitted using SMTP AUTH at out007.verizon.net from
	[151.200.47.157] at Tue, 9 Nov 2004 20:35:56 -0600
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200411/93
X-Sequence-Number: 19412

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




From sszabo@megazone.bigpanda.com Wed Nov  10 03:19:46 2004
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 680933A410E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 03:19:46 +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 75634-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Nov 2004 03:19:36 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 2A0F53A40AD
	for <pgsql-sql@postgresql.org>; Wed, 10 Nov 2004 03:19:39 +0000 (GMT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 5C340358AA; Tue,  9 Nov 2004 19:19:37 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 5A84E3587B; Tue,  9 Nov 2004 19:19:37 -0800 (PST)
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
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200411/94
X-Sequence-Number: 19413

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.



From Karsten.Hilbert@gmx.net Sun Nov  14 09:00:28 2004
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 8D0463A4C58
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 14 Nov 2004 09:00:28 +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 15744-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 14 Nov 2004 09:00:19 +0000 (GMT)
Received: from mail.gmx.net (pop.gmx.net [213.165.64.20])
	by svr1.postgresql.org (Postfix) with SMTP id 25E313A4C68
	for <pgsql-sql@postgresql.org>; Sun, 14 Nov 2004 09:00:17 +0000 (GMT)
Received: (qmail 624 invoked by uid 65534); 14 Nov 2004 09:00:16 -0000
Received: from p5087FAA4.dip0.t-ipconnect.de (EHLO hermes.hilbert.loc)
	(80.135.250.164)
	by mail.gmx.net (mp016) with SMTP; 14 Nov 2004 10:00:16 +0100
X-Authenticated: #1433807
Received: (from ncq@localhost)
	by hermes.hilbert.loc (8.11.6/8.11.6/SuSE Linux 0.5) id iAE8lOZ10739
	for pgsql-sql@postgresql.org; Sun, 14 Nov 2004 09:47:24 +0100
Date: Sun, 14 Nov 2004 09:47:24 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: UPDATE/INSERT on multiple co-dependent tables
Message-ID: <20041114094724.J568@hermes.hilbert.loc>
References: <1100053572.9414.142.camel@scnmaster.sleekcollar.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1100053572.9414.142.camel@scnmaster.sleekcollar.com>
User-Agent: Mutt/1.3.22.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200411/135
X-Sequence-Number: 19454

> 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