gis Database

Parameters

Name	Type	Mode

Definition

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;

trg_avant_modification_reparation

Parameters

Definition