My trigger function consists of collecting the vertices of a polyline table "depart_bt", searching for the point entities of the table “support_bt” which coincide with the vertices of the depart_bt table, then retrieving the value of the "code_dep" attribute (unique identifier of the depart_bt table) of each entity found and report it in the "code_dep" attribute (foreign key in the support_bt table).
CREATE OR REPLACE FUNCTION vertex_troncon() RETURNS TRIGGER language plpgsql AS $$ DECLARE box_pt geometry(point); BEGIN IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN IF NEW.code_dep IS NOT NULL THEN UPDATE support_bt SET code_dep = NEW.code_dep WHERE support_bt.geom_point IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom); RETURN NULL; END IF; ELSE RAISE WARNING 'Other action occurred: %, at %', TG_OP, now(); RETURN NULL; END IF; END; $$ ; -- creation du trigger DROP TRIGGER IF EXISTS vertex_dep ON depart_bt; CREATE TRIGGER vertex_dep AFTER INSERT OR UPDATE ON depart_bt FOR EACH ROW EXECUTE PROCEDURE vertex_troncon();
My first code works well, but it has the disadvantage of iterating through all points of "support_bt" which may affect the performance of the trigger. So, I added a bounding box to limit the search area. However, I noticed that the bounding box does not work fine and all points in the support_bt table are selected.
/*UPDATE support_bt SET code_dep = NEW.code_dep WHERE support_bt.geom_point IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom);*/ SELECT geom_point INTO box_pt FROM support_bt WHERE ST_contains((SELECT ST_SetSRID(ST_Extent(NEW.geom_line),32632)), support_bt.geom_point); UPDATE support_bt SET code_dep = NEW.code_dep WHERE box_pt IN (SELECT (ST_DumpPoints(NEW.geom_line)).geom);