[postgis-users] Trigger Function for Updating Geom

[Willem Buitendyk]

Success.  To get it to update the geom I had to change my trigger to 
insert before - not after. 

>>
>>
>> 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
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>