How can I ensure that in a insert trigger a column gets filled before other column? 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.
If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM 
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;


Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS 
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM 
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM 
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$ 
LANGUAGE plpgsql
;



the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)