Le portail francophone de la géomatique

CREATE OR REPLACE FUNCTION f_anomalie_cana()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    
     INSERT INTO
      anomalies(id_objet,type_objet,type_anomalie,date,heure,anomalie)
      select tt.objectid, 
      (select 
       t.table_name
        from information_schema.tables t
        inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema
        where c.column_name = 'anomalie' and t.table_name ='cana'),
        case 
        when tt.anomalie=1 then 'effondrement' 
        when tt.anomalie=2 then 'racines' 
        when tt.anomalie=3 then 'fissures' 
        when tt.anomalie=6 then 'réparée' 
        when tt.anomalie=7 then 'déplacements d''assemblage' 
        when tt.anomalie=8 then 'depot' 
        when tt.anomalie=9 then 'sable' 
        when tt.anomalie=10 then 'eau stagnante' 
        when tt.anomalie=11 then 'perforation' 
        when tt.anomalie=12 then 'eau claire parasite'   
        when tt.anomalie=17 then 'poinconnement' 
        when tt.anomalie=18 then 'curage' 
        else null 
        end,
        current_date, 
        current_time,
        tt.anomalie 
        from cana tt where tt.anomalie = new.anomalie;
        
RETURN new;

END;
$BODY$;

et le trigger: 

CREATE OR REPLACE TRIGGER trg_anomalie_cana
    AFTER INSERT OR UPDATE 
    ON cana
    FOR EACH ROW
    EXECUTE FUNCTION f_anomalie_cana();