Split lines against polygons in a PostGIS trigger

Question

I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of segment on communes features on update of geom field or insert of a new segment. Two situations:

• insertion: the new feature should be split on the boundaries of communes polygons, and each line resulting should have some attributes of the polygon it's part of
• update: the existing feature should continue to exist, its id should not change, but it should have a new geom split on polygons boundaries, and new attributes if necessary. New features should be inserted in the same table for each new polygon the NEW geometry intersects.

My code below updates the existing feature accordingly, but lines that were supposed to be inserted in the table aren't. I think it has something to do with the way I handle the returns of the function, but I tried many combinations without success.

CREATE OR REPLACE FUNCTION split_segments_on_communes_and_update_collectivities_codes() 
RETURNS TRIGGER AS
$BODY$
DECLARE
rec RECORD;
BEGIN
    FOR rec IN
        SELECT c.insee_com AS insee_com,
               '0' || c.insee_dep AS insee_dep,
               c.insee_reg AS insee_reg,
               c.code_epci AS code_epci,
               (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom,
               (ST_Dump(ST_Intersection(NEW.geom, c.geom))).path[0] AS "path",
               NEW.id_statut,
               NEW.propriete,
               NEW.author,
               NEW.create_date
          FROM donnees_externes.commune c
          WHERE ST_Intersects(NEW.geom, c.geom)
    LOOP
        IF rec."path" = 1 OR rec."path" IS NULL THEN
            NEW.geom = rec.geom;
            NEW.code_reg = rec.insee_reg;
            NEW.code_dep = rec.insee_dep;
            NEW.code_epci = rec.code_epci;
            NEW.code_commune = rec.insee_com;
        ELSE
            INSERT INTO reference.segment (geom,
                                           id_statut,
                                           propriete,
                                           code_epci,
                                           code_dep,
                                           code_reg,
                                           author,
                                           create_date,
                                           code_commune)
            VALUES (
                   rec.geom,
                   NEW.id_statut,
                   NEW.propriete,
                   rec.code_epci,
                   rec.insee_dep,
                   rec.insee_reg,
                   NEW.author,
                   NEW.create_date,
                   rec.insee_com);
        END IF;
    END LOOP;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER split_segments_on_communes_and_update_collectivities_codes
 BEFORE INSERT
    OR UPDATE OF geom
    ON reference.segment
 FOR EACH ROW
 WHEN (pg_trigger_depth() < 1)
 EXECUTE PROCEDURE split_segments_on_communes_and_update_collectivities_codes();

Answer 1

The problem was in the path definition in that line: (ST_Dump(ST_Intersection(NEW.geom, c.geom))).path[0] AS "path".

Here is the correct way to do it:

WITH parts AS (
            SELECT c.insee_com AS insee_com,
                   '0' || c.insee_dep AS insee_dep,
                   c.insee_reg AS insee_reg,
                   c.code_epci AS code_epci,
                   (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom,
                   NEW.id_statut,
                   NEW.propriete,
                   NEW.author,
                   NEW.create_date
              FROM donnees_externes.commune c
             WHERE ST_Intersects(NEW.geom, c.geom)
        )
        SELECT *,
               ROW_NUMBER() OVER () AS "path"
          FROM parts

And the full function:

CREATE OR REPLACE FUNCTION reference.split_segments_on_communes_and_update_collectivities_codes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
rec RECORD;
newgeom geometry;
BEGIN
    FOR rec IN
        WITH parts AS (
            SELECT c.insee_com AS insee_com,
                   '0' || c.insee_dep,
                   c.insee_reg AS insee_reg,
                   c.code_epci AS code_epci,
                   (ST_Dump(ST_Intersection(NEW.geom, c.geom))).geom AS geom
              FROM donnees_externes.commune c
             WHERE ST_Intersects(NEW.geom, c.geom)
        )
        SELECT *,
               ROW_NUMBER() OVER () AS "path"
          FROM parts
    LOOP 
        IF ST_GeometryType(rec.geom) != 'ST_Point' THEN
           IF rec."path" = 1 THEN
              NEW.geom := rec.geom;
              NEW.code_reg := rec.insee_reg;
              NEW.code_dep := rec.insee_dep;
              NEW.code_epci := rec.code_epci;
              NEW.code_commune := rec.insee_com;
           ELSE
              INSERT INTO reference.segment (geom,
                                             id_statut,
                                             propriete,
                                             code_epci,
                                             code_dep,
                                             code_reg,
                                             author,
                                             create_date,
                                             code_commune)
              VALUES (
                     rec.geom,
                     NEW.id_statut,
                     NEW.propriete,
                     rec.code_epci,
                     rec.insee_dep,
                     rec.insee_reg,
                     NEW.author,
                     NEW.create_date,
                     rec.insee_com);
           END IF;
        END IF;
    END LOOP;
RETURN NEW;
END;
$function$
;