The bigger picture: tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each pipeline into segments of maximum 1 meter distance. Then, for each segment I am doing a spatial query that selects the closest street (in a maximum 20 meter range). Then, by finding out which street appears the most in all the segments that belong to that specific pipeline(by id), I am inserting the found street name into the pipeline feature.
In a trigger (on insert, update) the function is making a join based on the unique id column.
Having tableX (id, name)
the function that gets triggered looks like this:
CREATE or REPLACE function T_function() RETURNS trigger AS $T_function$ BEGIN NEW.name =(SELECT ......... WHERE other_table.id = NEW.id .....); RETURN NEW; END; $T_function$ LANGUAGE plpgsql;
As you can see, the function does a join based on the newly created id. It works only on update, probably because the NEW.name does not use the new id. How can I enforce saving/using/generating NEW.id before NEW.name ..?
later edit describing the tables (with postgis geometry) and what I am trying to do:
CREATE TABLE tableX ( id integer, name varchar, shape geometry ); -- geometry - linestrig CREATE TABLE tableY ( id integer, name varchar, shape geometry ); -- geometry - linestring CREATE or REPLACE function T_function RETURNS trigger AS $T_function$ BEGIN NEW.id = NEW.id; NEW.name = ( SELECT name FROM ( SELECT id, name, cnt FROM ( SELECT id, name, COUNT(*) AS cnt, RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk FROM ( SELECT loc.id, r.name, ST_Distance(r.shape, loc.shape) AS dist FROM ( SELECT id, (st_dumpPoints(st_segmentize(shape, 1))).geom AS shape FROM tableX ) as loc INNER JOIN tableY AS r ON ST_DWithin(r.shape, loc.shape, 20) WHERE loc.id = NEW.id ORDER BY loc.id, ST_Distance(loc.shape, loc.shape) ) AS coco GROUP BY id, name ) AS tg WHERE rnk = 1 ) as b ); RETURN NEW; END; $T_function$ LANGUAGE plpgsql ; CREATE TRIGGER T_function_trigger BEFORE INSERT OR UPDATE ON tableX FOR EACH ROW EXECUTE PROCEDURE T_function() ;
I am trying to do a spatial query and to fill tableX.name column with the name from tableY.name column based on the nearest neighbor approach. It only works on update for now.