[postgis-users] Trigger Function for Updating Geom

[Willem Buitendyk]

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