[postgis-users] Trigger Function for Updating Geom

[Bruce Rindahl]

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