[postgis-users] Points and lines

[Mark Cave-Ayland]

Tuomas Ruohonen wrote:

> What is wrong in my function, it doesnt stop or should it take so 
> loooong... ? Minutes... Trying to insert to lines in table
> 
> CREATE OR REPLACE FUNCTION make_point() RETURNS TRIGGER AS $start_point$
>     BEGIN
>    
>       UPDATE positionreports SET the_geom = SetSRID(st_makepoint
>     ("longitude","latitude"), 4326);
> 
>         RETURN NULL; -- result is ignored since this is an AFTER trigger
>     END;
> $start_point$ LANGUAGE plpgsql;
> 
> CREATE TRIGGER start_point
> AFTER INSERT OR UPDATE ON positionreports
>     FOR EACH ROW EXECUTE PROCEDURE make_point();

Look carefully at your stored procedure - you're updating the contents 
of the entire table every time someone modifies a single row.

Take a look at the documentation here: 
http://www.postgresql.org/docs/8.4/interactive/plpgsql-trigger.html. You 
should be using the NEW and OLD pseudotypes to change to contents of a 
single row.


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs