This trigger must compute a new geometry for each inserted or updated point. This new geometry must be calculated as the projection of the geometry of the currently inserted feature point onto the nearest line of another table containing line features.
What I've done
The trigger is as follow, it is correctly fired as the
EXPLAIN ANALYZE shows after a new insertion:
no errors are thrown when I insert a new point, but the projected geometry column
proj_geom stays empty:
CREATE OR REPLACE FUNCTION project_funct() RETURNS trigger AS $$ BEGIN -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time -- but for the moment I only put the focus on the new geometry proj_geom: NEW.projected_geom := ( SELECT sub.proj_geom FROM ( SELECT points.id AS point_id, schema.lines.id AS line_id, ST_Distance(schema.lines.geom, NEW.geom) AS dist, ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom FROM schema.line, points -- Something is weird to me there, I should probably not have this WHERE statement here as the -- trigger should be used on a single row at a time, hence with a single ID for each execution: WHERE points.id = NEW.id ORDER BY dist LIMIT 1 ) AS sub ); RETURN NEW; END $$ LANGUAGE 'plpgsql'; CREATE TRIGGER proj_trigger -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and -- INSERT but this may not be a good idea?: BEFORE INSERT OR UPDATE ON points FOR EACH ROW EXECUTE PROCEDURE project_funct();
As I discovered triggers yesterday evening, and even if I've done my best, my code is probably full of mistakes.
I also noticed some SQL scripts that I already have and which are actually working, ... doesn't work anymore in this fancy(?) way of writing triggers (e.g. I don't know the meaning of the double dollar sign
If I take out the inner most
SELECT statement, and replace the word
NEW by the actual name of the points table, the query, when run directly in pgAdmin with a specified ID such as
WHERE points.id = 41 returns the correct results:
In the trigger, my first comment relates to the fact I wish to be able to update at the same time the
proj_geom fields of the table
points each time an observation is inserted.
How could I fix my code, at least to be able to get the new geometry?
I'm pretty sure I'm missing some tiny details (probably where I left some comments in the code where I have that actual feeling) but I can't figure them out and I've done too much hit'n'try now with the trigger function (none of them worked as good as the one here above).