trg_avant_modification_reparation

DECLARE

    organisme text;

    table_canalisation text;

    table_ouvrage text;

    table_appareil text;

BEGIN

​

    -- Calcul de l'identifiant si besoin

    IF NEW.idrepar IS NULL OR trim(NEW.idrepar) = '' OR trim(NEW.idrepar) = 'INCONNU' THEN

        NEW.idrepar := raepa.generate_oid(TG_TABLE_NAME::text)::character varying;

    END IF;

​

    -- Calcul de la géométrie à partir de X et Y

    IF ( TG_OP = 'UPDATE' AND NEW.x IS NOT NULL AND NEW.y IS NOT NULL AND ( NEW.x != OLD.x OR NEW.y != OLD.y ) )

    OR ( TG_OP = 'INSERT' AND NEW.x IS NOT NULL AND NEW.y IS NOT NULL ) THEN

        NEW.geom := ST_SetSRID(ST_Makepoint(NEW.x, NEW.y), 2154);

        RAISE NOTICE 'appareil % - X ou Y changé -> changement geom vers POINT(% %)', NEW.idrepar, NEW.x, NEW.y;

    END IF;

​

    IF ( TG_OP = 'UPDATE' AND NOT ST_Equals(NEW.geom, OLD.geom) ) OR TG_OP = 'INSERT' THEN

​

        -- Calcul des X et Y si besoin

        NEW.x := ST_X(NEW.geom)::numeric(10,3);

        NEW.y := ST_Y(NEW.geom)::numeric(10,3);

    END IF;

​

​

        -- idsuprepar

    table_canalisation = 'raepa.raepa_canalass_l';

    IF TG_TABLE_NAME = 'raepa_reparaep_p' THEN

        table_canalisation = 'raepa.raepa_canalaep_l';

    END IF;

​

    table_ouvrage = 'raepa.raepa_ouvrass_p';

    IF TG_TABLE_NAME = 'raepa_reparaep_p' THEN

        table_ouvrage = 'raepa.raepa_ouvraep_p';

    END IF;

​

    table_appareil = 'raepa.raepa_apparass_p';

    IF TG_TABLE_NAME = 'raepa_reparaep_p' THEN

        table_appareil = 'raepa.raepa_apparaep_p';

    END IF;

​

    IF NEW.idsuprepar IS NULL OR trim(NEW.idsuprepar) = '' OR trim(NEW.idsuprepar) = 'INCONNU' THEN

    IF (trim(NEW.supprepare) = '01') THEN

        EXECUTE format(

            '

            SELECT COALESCE(string_agg(c.idcana, '','' ORDER BY idcana), ''INCONNU'')

            FROM %s AS c

            WHERE ST_DWithin(c.geom, ''%s''::geometry, 0.05)

            ',

            table_canalisation,

            NEW.geom

        )

        INTO NEW.idsuprepar;

​

​

    ELSIF (trim(NEW.supprepare) = '02') THEN

        EXECUTE format(

            '

            SELECT COALESCE(string_agg(a.idappareil, '','' ORDER BY idappareil), ''INCONNU'')

            FROM %s AS a

            WHERE ST_DWithin(a.geom, ''%s''::geometry, 0.05)

            ',

            table_appareil,

            NEW.geom

        )

        INTO NEW.idsuprepar;

​

    ELSIF (trim(NEW.supprepare) = '03') THEN

        EXECUTE format(

            '

            SELECT COALESCE(string_agg(o.idouvrage, '','' ORDER BY idouvrage), ''INCONNU'')

            FROM %s AS o

            WHERE ST_DWithin(o.geom, ''%s''::geometry, 0.05)

            ',

            table_ouvrage,

            NEW.geom

        )

        INTO NEW.idsuprepar;

​

    ELSE NEW.idsuprepar:='INCONNU';

    END IF;

    END IF;

​

​

    -- Métadonnées

    IF (NEW.mouvrage IS NULL) THEN

        -- Récupération du gestionnaire

        SELECT INTO organisme

        s.nom

        FROM raepa.sys_organisme_gestionnaire s

        WHERE actif IS TRUE

        ORDER BY id DESC

        LIMIT 1;

    END IF;

​

    IF NEW.mouvrage IS NULL THEN

        NEW.mouvrage := organisme;

    END IF;

​

    RETURN NEW;

​

END;

​