[postgis-users] Trigger Function for Updating Geom

[Kevin Neufeld]

:) well, yes, that would make sense. 

I commonly use rules with table partitioning ... where I'm inserting 
into different tables.  I suppose you could set it up so that all 
inserts are performed by inserting into a second tmp_hyd table, but that 
sounds like a hack.

ok, never mind. :)

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