<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18783">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2 face=Arial>Leo</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Following is the update that I wish to deploy and I
have used it as an AFTER INSERT on the parent table. When the object
(process_id) has one attrribute (fluid_id) this works well. </FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>With two fluid_id attributes the update
lands both attributes on the same geometry.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>I don't understand what needs to be done to loop
through the fluid_ids using recursive.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial> update p_id.image<BR> set the_geom =
<BR> (select
st_translate(graphics.utilities_dgm.the_geom,<BR> st_x(st_centroid(graphics.spatial_ref.line_the_geom))
-
<BR> st_x(st_centroid(graphics.utilities_dgm.the_geom)),<BR> st_y(p_id.p_id.three)
+ graphics.ip_matrix.point_offset
-<BR> st_y(st_centroid(graphics.utilities_dgm.the_geom)))<BR> from
graphics.utilities_dgm, p_id.processes, p_id.p_id, graphics.spatial_ref,
p_id.image, processes_count, graphics.ip_matrix<BR> where
graphics.utilities_dgm.utilities_description = 'Text Arrow' <BR> and cast
(graphics.utilities_dgm.orientation as integer) =
graphics.ip_matrix.text_arrow_orientation<BR> and
graphics.ip_matrix.ip_number = p_id.p_id.number_of_ips<BR> and
p_id.image.p_id_id = p_id.p_id.p_id_id<BR> and
graphics.spatial_ref.description = p_id.image.pid_outline<BR> and
p_id.image.description = 'P&ID Outline'<BR> and p_id.p_id.p_id_id =
processes_count.p_id_id<BR> and graphics.spatial_ref.orientation =
graphics.ip_matrix.text_arrow_orientation<BR> and
graphics.ip_matrix.ip_number = p_id.processes.ip_number <BR> and
p_id.p_id.p_id_id = processes_count.p_id_id<BR> and
graphics.ip_matrix.spatial_ref = p_id.p_id.proc_graphic_position)<BR> from
p_id.processes, processes_count<BR> where p_id.processes.p_id_id =
processes_count.p_id_id<BR> and p_id.processes.fluid_id =
p_id.image.fluid_id<BR> and p_id.image.description =
'ip_op_Arrow_name';</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>The system I have deployed to establish the
position of the process objects is based on a number or processes count using IF
statements. I am sure there is a less onerous method but, for the time being it
is working without using an AFTER UPDATE. That is, unless AFTER UPDATE is needed
to accomplish the recursive operation.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Bob</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: black"><B>From:</B>
<A title=lr@pcorp.us href="mailto:lr@pcorp.us">Paragon Corporation</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">'PostGIS Users
Discussion'</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, July 21, 2009 11:38
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [postgis-users] Loop or
Cursor</DIV>
<DIV><BR></DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial>Bob,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial>For this kind of thing -- we usually use a database trigger
on the AFTER INSERT row event of the table in question. You would also
probably need an AFTER UPDATE trigger as well since as a child object is being
updated,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial>you may want to update the parent if the update condition
affects the structure/position of the parent. So in essence you would
have a RECURSIVE trigger that keeps on recursing until the final has no
parents. Though you'd need to be careful to make sure you don't have
cyclical redudancies that could trigger an infinite
recursion.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial>Perhaps if you provided the update statement you are trying
to run we could be of more help.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=203503206-22072009><FONT color=#0000ff
size=2 face=Arial>Leo</FONT></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Bob
Pawley<BR><B>Sent:</B> Tuesday, July 21, 2009 11:19 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Loop or
Cursor<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT size=2 face=Arial>Hi Leo</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>I'm developing a geometric system as opposed to
geographic (basically an engineering drawing).</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>I have a fixed field, representing a peice of
paper, within which the user can add one to four objects.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>These object are placed within the field
according to the total number of objects.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>The first object 'A' is placed in the center and
then the user adds lines (one to eight at the moment) as attributes to
the object.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>When the user adds a second object, I want to
update the attributes of object A to satisfy the new placement of object
A.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>When the user places a third object then both,
object A and B, need to be updated.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>At the moment, my updates haven't worked as I
get either a null or multiple return.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Any suggestions for updating the lines for each
object would be greatly appreciated.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Thanks</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Bob</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: black"><B>From:</B>
<A title=lr@pcorp.us href="mailto:lr@pcorp.us">Paragon Corporation</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=postgis-users@postgis.refractions.net
href="mailto:postgis-users@postgis.refractions.net">'PostGIS Users
Discussion'</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, July 20, 2009 7:45
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [postgis-users] Loop or
Cursor</DIV>
<DIV><BR></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>What kind of operation? Even an update
statement might suffice. There are many ways to cleverly control the
flow of an update that do not necessitate doing a loop or
cursor.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>Also keep in mind even within a function used in
a SELECT statement of a query, PostgreSQL allows you to run
updates and do all sorts of crazy things like launch perl scripts and bash
scripts. So for example you can mail to your whole membership simple
by doing this</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>SELECT member_name, send_mail(member_id) As
did_it_send</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>FROM membership</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>WHERE gross_income > 1000000 and active =
true</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>It is very convenient for making rule
processors.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial>Leo</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=815403702-21072009><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Bob Pawley<BR><B>Sent:</B> Monday, July 20, 2009 1:43 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users] Loop or
Cursor<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT size=2 face=Arial>Hi</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>I need to access a number of rows, one at a
time to perform an operation on each.</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Under what conditions would I consider using a
loop command, rather than (or with) a cursor?</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>Bob</FONT></DIV>
<P>
<HR>
<P></P>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>postgis-users
mailing
list<BR>postgis-users@postgis.refractions.net<BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></BODY></HTML>