Leo 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.
With two fluid_id attributes the update lands both attributes on the same geometry. I don't understand what needs to be done to loop through the fluid_ids using recursive. update p_id.image set the_geom = (select st_translate(graphics.utilities_dgm.the_geom, st_x(st_centroid(graphics.spatial_ref.line_the_geom)) - st_x(st_centroid(graphics.utilities_dgm.the_geom)), st_y(p_id.p_id.three) + graphics.ip_matrix.point_offset - st_y(st_centroid(graphics.utilities_dgm.the_geom))) from graphics.utilities_dgm, p_id.processes, p_id.p_id, graphics.spatial_ref, p_id.image, processes_count, graphics.ip_matrix where graphics.utilities_dgm.utilities_description = 'Text Arrow' and cast (graphics.utilities_dgm.orientation as integer) = graphics.ip_matrix.text_arrow_orientation and graphics.ip_matrix.ip_number = p_id.p_id.number_of_ips and p_id.image.p_id_id = p_id.p_id.p_id_id and graphics.spatial_ref.description = p_id.image.pid_outline and p_id.image.description = 'P&ID Outline' and p_id.p_id.p_id_id = processes_count.p_id_id and graphics.spatial_ref.orientation = graphics.ip_matrix.text_arrow_orientation and graphics.ip_matrix.ip_number = p_id.processes.ip_number and p_id.p_id.p_id_id = processes_count.p_id_id and graphics.ip_matrix.spatial_ref = p_id.p_id.proc_graphic_position) from p_id.processes, processes_count where p_id.processes.p_id_id = processes_count.p_id_id and p_id.processes.fluid_id = p_id.image.fluid_id and p_id.image.description = 'ip_op_Arrow_name'; 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. Bob ----- Original Message ----- From: Paragon Corporation To: 'PostGIS Users Discussion' Sent: Tuesday, July 21, 2009 11:38 PM Subject: Re: [postgis-users] Loop or Cursor Bob, 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, 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. Perhaps if you provided the update statement you are trying to run we could be of more help. Leo ------------------------------------------------------------------------------ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bob Pawley Sent: Tuesday, July 21, 2009 11:19 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Loop or Cursor Hi Leo I'm developing a geometric system as opposed to geographic (basically an engineering drawing). I have a fixed field, representing a peice of paper, within which the user can add one to four objects. These object are placed within the field according to the total number of objects. 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. When the user adds a second object, I want to update the attributes of object A to satisfy the new placement of object A. When the user places a third object then both, object A and B, need to be updated. At the moment, my updates haven't worked as I get either a null or multiple return. Any suggestions for updating the lines for each object would be greatly appreciated. Thanks Bob ----- Original Message ----- From: Paragon Corporation To: 'PostGIS Users Discussion' Sent: Monday, July 20, 2009 7:45 PM Subject: Re: [postgis-users] Loop or Cursor 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. 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 SELECT member_name, send_mail(member_id) As did_it_send FROM membership WHERE gross_income > 1000000 and active = true It is very convenient for making rule processors. Leo ---------------------------------------------------------------------------- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bob Pawley Sent: Monday, July 20, 2009 1:43 PM To: PostGIS Users Discussion Subject: [postgis-users] Loop or Cursor Hi I need to access a number of rows, one at a time to perform an operation on each. Under what conditions would I consider using a loop command, rather than (or with) a cursor? Bob ---------------------------------------------------------------------------- _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ------------------------------------------------------------------------------ _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
