[postgis-users] Trigger Function for Updating Geom

Wed Jan 23 09:07:45 PST 2008

I tried using the rule instead but get this as an error message:
Error - 2008/01/23 09:02:49 - Line 1: ERROR:  infinite recursion 
detected in rules for relation "hyd"

Kevin Neufeld wrote:
> Also, in your case, I would recommend using a RULE instead of a 
> TRIGGER - it could be much faster.
> http://www.postgresql.org/docs/8.2/static/rules-triggers.html
> http://www.postgresql.org/docs/8.2/static/sql-createrule.html
>
> A trigger is fired on every row affected. So in your case, if you 
> update a row in your table, postgres inserts a new row into the table 
> and deletes the old row flagging it as a dead tuple.  Then the trigger 
> fires on the row and another insert and delete operation is 
> performed.  That's 2 inserts and 2 deletes - a rather expensive update 
> operation.
>
> On the other hand, a rule simply rewrites the query once at the 
> beginning, leaving you with one update operation.
>
> Following the docs, your rule might be something like:
> CREATE OR REPLACE RULE make_geom_rule AS
>  ON INSERT TO hyd
>  DO INSTEAD
>    INSERT INTO hyd (field1, ..., hydgeom) VALUES
>      (NEW.field1, ..., SetSRID(ST_MakePoint(NEW.longitude, 
> NEW.latitude), 8307));
>
> -- Kevin
>
> Obe, Regina wrote:
>> It should be
>> NEW.hydgeom := 
>> You have an = there (which would evaluate to a boolean of some sort)
>> instead of plpgsql assignment :=
>>
>> Also I think using makepoint is more efficient,shorter to write, and
>> easier to read
>>
>> So would be
>>
>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>> $BODY$
>> BEGIN
>> NEW.hydgeom := SetSRID(makepoint(NEW.longitude, NEW.latitude), 8307);
>> RETURN NEW;
>> END
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> Hope that helps,
>> Regina
>>
>>
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Willem Buitendyk
>> Sent: Wednesday, January 23, 2008 4:20 AM
>> To: rindahl at lrcwe.com; PostGIS Users Discussion
>> Subject: Re: [postgis-users] Trigger Function for Updating Geom
>>
>> What you revised makes more sense;  however, the problem now is that the
>>
>> geom column does not update at all.  I modified the function to include
>> RETURN NEW;
>>
>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>> $BODY$
>> BEGIN
>> NEW.hydgeom = GeometryFromText('POINT (' || cast(NEW.longitude as 
>> varchar(10)) || ' ' || cast(NEW.latitude as varchar(10)) || ')', 8307);
>> RETURN NEW;
>> END
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER make_geom_trigger AFTER INSERT OR UPDATE ON hyd FOR 
>> EACH ROW EXECUTE PROCEDURE make_geom();
>>
>>
>> Any ideas?
>>
>> Cheers
>>
>>
>>
>>
>> Bruce Rindahl wrote:
>>  
>>> Try:
>>>
>>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>>> $BODY$
>>> BEGIN
>>> NEW.hydgeom = GeometryFromText('POINT (' || cast(NEW.longitude as 
>>> varchar(10)) || ' ' || cast(NEW.latitude as varchar(10)) || ')',
>>>     
>> 8307);
>>  
>>> END
>>> $BODY$
>>> LANGUAGE plpgsql;
>>>   This will update each row as it is processed.  What you were doing 
>>> was
>>> trying to update EVERY row as EACH one was processed.
>>>
>>> Bruce Rindahl
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net
>>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>>>     
>> Willem
>>  
>>> Buitendyk
>>> Sent: Tuesday, January 22, 2008 2:17 PM
>>> To: postgis-users at postgis.refractions.net
>>> Subject: [postgis-users] Trigger Function for Updating Geom
>>>
>>> Hi there,
>>>
>>> I am trying to figure out how to go about creating a trigger that 
>>> updates the geom on each insert.  Currently, I am using the 
>>> following update statement on the table after bulk loading data in:
>>>
>>> update temp_hyd set hydgeom = GeometryFromText('POINT (' || 
>>> cast(longitude as varchar(10)) || ' ' || cast(latitude as varchar(10))
>>>     
>>
>>  
>>> || ')', 8307);
>>>
>>> I admit to feeling a little lost as I am only now just starting to 
>>> migrate GIS data into a server.  So using relational databases are a 
>>> little new to me (and I don't think Access counts).  I am hoping that
>>>     
>> only
>>  
>>> updating those newly inserted records will be a whole lot faster 
>>> then redoing the whole table.
>>>
>>> Here is what I have so far:
>>>
>>>
>>> CREATE or replace FUNCTION make_geom () RETURNS trigger as
>>> $BODY$
>>> BEGIN
>>> update hyd set hydgeom = GeometryFromText('POINT (' || cast(longitude
>>>     
>> as  
>>> varchar(10)) || ' ' || cast(latitude as varchar(10)) || ')', 8307);
>>> END
>>> $BODY$
>>> LANGUAGE plpgsql;
>>>
>>> CREATE TRIGGER make_geom_trigger AFTER INSERT OR UPDATE ON hyd FOR
>>>     
>> EACH  
>>> ROW EXECUTE PROCEDURE make_geom();
>>>
>>> And of course it doesn't work.  I get the following after doing an 
>>> insert statement:
>>>
>>> ERROR: stack depth limit exceeded
>>> SQL state: 54001
>>> Hint: Increase the configuration parameter "max_stack_depth", after 
>>> ensuring the platform's stack depth limit is adequate.
>>> Context: SQL statement "update hyd set hydgeom =
>>>     
>> GeometryFromText('POINT  
>>> (' || cast(longitude as varchar(10)) || ' ' || cast(latitude as 
>>> varchar(10)) || ')', 8307)"
>>>
>>> Appreciate any help with this,
>>>
>>>
>>> Willem
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>       
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>   
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>