<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6619.12">
<TITLE>RE: [postgis-users] Creating a Flow Diagram</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>This from a postgres post on a similar topic from Tom Lane:<BR>
<BR>
> Rules are macros, which means that expansion has to terminate<BR>
> statically, not dynamically. For the particular purpose you seem to<BR>
> have here, it'd be a lot more manageable and a lot more efficient<BR>
> to use a BEFORE UPDATE trigger instead of a rule.<BR>
<BR>
I will speculate on what this means, and let the more learned database gurus on the list correct me:<BR>
<BR>
I think this means that a RULE must execute in such a way as to avoid calling itself again, hence the "static" termination. The BEFORE UPDATE, or I think perhaps more advisable in this case, AFTER UPDATE trigger is allowed to pass itself on to subsequent procedures, whereas the RULE is not.<BR>
<BR>
r.b.<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Sufficool, Stanley<BR>
Sent: Thu 6/5/2008 2:15 PM<BR>
To: PostGIS Users Discussion<BR>
Cc: <BR>
Subject: RE: [postgis-users] Creating a Flow Diagram<BR>
<BR>
OK, I'm trying to come up with a solid solution to all this since it<BR>
roughly applies to some things I'm doing with attached objects and<BR>
location tracking.<BR>
<BR>
This rule is resulting in infinite recursion when there is no reason for<BR>
the rule to even execute.<BR>
<BR>
Am I understanding the rules system wrong or should this be a trigger<BR>
function?<BR>
<BR>
-------------------<BR>
<BR>
create or replace rule entity_update AS ON update TO entity<BR>
WHERE NOT (st_AsText(OLD.the_geom) ~= st_AsText(NEW.the_geom)) -- Only<BR>
if geometry was modifed (I'm sure there is a more appropriate operator<BR>
here)<BR>
DO UPDATE entity<BR>
SET the_geom = st_translate(<BR>
-- Center the child on parent geometry<BR>
st_translate(<BR>
the_geom,<BR>
st_x(st_centroid(NEW.the_geom)) -<BR>
st_x(st_centroid(the_geom)), <BR>
st_y(st_centroid(NEW.the_geom)) -<BR>
st_y(st_centroid(the_geom)),<BR>
st_z(st_centroid(NEW.the_geom)) -<BR>
st_z(st_centroid(the_geom)) <BR>
),<BR>
--Translate to relative position of parent<BR>
( st_xmax(NEW.the_geom) - st_xmin(NEW.the_geom) ) *<BR>
ratio_x,<BR>
( st_ymax(NEW.the_geom) - st_ymin(NEW.the_geom) ) *<BR>
ratio_y,<BR>
( st_zmax(NEW.the_geom) - st_zmin(NEW.the_geom) ) *<BR>
ratio_z<BR>
)<BR>
WHERE parent_entity_id = NEW.entity_id --Update all children of<BR>
the modified parent record<BR>
;<BR>
<BR>
-- NOW LETS touch the parent<BR>
update entity SET the_geom = st_translate(the_geom, 0, 0) where<BR>
entity_id = (SELECT MIN(entity_id) FROM entity)<BR>
<BR>
<BR>
ERROR: infinite recursion detected in rules for relation "entity"<BR>
<BR>
********** Error **********<BR>
<BR>
ERROR: infinite recursion detected in rules for relation "entity"<BR>
SQL state: 42P17<BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>