[postgis-users] Trigger Function for Updating Geom

[Willem Buitendyk]

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
>
>