Le portail francophone de la géomatique

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

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$
;