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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Willem
Buitendyk
Sent: Tuesday, January 22, 2008 2:17 PM
To: postgis-users@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
