[postgis-users] Trigger Function for Updating Geom

[Obe, Regina]

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.