Forums PostgreSQL.fr

CREATE OR REPLACE RULE insert_on_vue AS
    ON insert TO vue
    DO INSTEAD insert into tableA (the_geom) values (NEW.the_geom);

CREATE RULE maj_vue AS
    ON UPDATE TOvue
    DO INSTEAD UPDATE tableA SET the_geom = NEW.the_geom WHERE A_id = A_id;

CREATE RULE suppr_on_vue AS
    ON DELETE TO vue
    DO INSTEAD DELETE FROM  tableA WHERE the_geom=OLD.the_geom ;

   
CREATE or replace FUNCTION test_maj() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP='INSERT')THEN
    ----------------------------------------------------TABLE_A--------------------------------------------------
        INSERT INTO tableA(
                A_id,            A_date_modif,        A_champ1,        A_champ2,           
                A_champ3,        A_champ4,        A_champ5,        A_champ6,
                A_champ7,        A_champ8,        A_champ9,        A_champ10,
                A_longueur        A_champ11,        A_champ12,        A_champ13)
        VALUES (
                NEW.A_id,        NEW.A_date_modif,    NEW.A_champ1,        NEW.A_champ2,
                NEW.A_champ3,             NEW.A_champ4,        NEW.A_champ5,        NEW.A_champ6,
                NEW.A_champ7,        NEW.A_champ8,        NEW.A_champ9,        NEW.A_champ10,
                NEW.A_longueur        NEW.A_champ11,        NEW.A_champ12,        NEW.A_champ13);
    ----------------------------------------------------A_CHAMP_B_ID------------------------------------------------
        CASE     WHEN vue.champB1 = "valeur1" THEN INSERT INTO tablesA(A_champB_id) VALUES (1);
                 WHEN vue.champB1 "valeur2" THEN INSERT INTO tablesA(A_champB_id) VALUES (2);
                WHEN vue.champB1 "valeur3" THEN INSERT INTO tablesA(A_champB_id) VALUES (3);
               WHEN vue.champB1 "valeur4" THEN INSERT INTO tablesA(A_champB_id) VALUES (4);
        ELSE RETURN NULL;
        END CASE;        
    ----------------------------------------------A_CHAMP_C_ID-----------------------------------------------
        CASE     WHEN vue.alias_champC1+champC2= "valeurC1_1-valeurC2_1" THEN INSERT INTO tablesA(A_champC_id) VALUES (1);
                 WHEN vue.alias_champC1+champC2= "valeurC1_2-valeurC2_2" THEN INSERT INTO tablesA(A_champC_id) VALUES (2);
                 WHEN vue.alias_champC1+champC2= "valeurC1_3-valeurC2_3" THEN INSERT INTO tablesA(A_champC_id) VALUES (3);
                WHEN vue.alias_champC1+champC2= "valeurC1_4-valeurC2_4" THEN INSERT INTO tablesA(A_champC_id) VALUES (4);
                WHEN vue.alias_champC1+champC2= "valeurC1_5-valeurC2_5" THEN INSERT INTO tablesA(A_champC_id) VALUES (5);
                 WHEN vue.alias_champC1+champC2= "valeurC1_6-valeurC2_6" THEN INSERT INTO tablesA(A_champC_id) VALUES (6);
                 WHEN vue.alias_champC1+champC2= "valeurC1_7-valeurC2_7" THEN INSERT INTO tablesA(A_champC_id) VALUES (7);
                 WHEN vue.alias_champC1+champC2= "valeurC1_8-valeurC2_8" THEN INSERT INTO tablesA(A_champC_id) VALUES (8);
                 WHEN vue.alias_champC1+champC2= "valeurC1_9-valeurC2_9" THEN INSERT INTO tablesA(A_champC_id) VALUES (9);
                WHEN vue.alias_champC1+champC2= "valeurC1_10-valeurC2_10" THEN INSERT INTO tablesA(A_champC_id) VALUES (10);
                WHEN vue.alias_champC1+champC2= "valeurC1_11-valeurC2_11" THEN INSERT INTO tablesA(A_champC_id) VALUES (11);
                WHEN vue.alias_champC1+champC2= "valeurC1_12-valeurC2_12" THEN INSERT INTO tablesA(A_champC_id) VALUES (12);
                WHEN vue.alias_champC1+champC2= "valeurC1_13-valeurC2_13" THEN INSERT INTO tablesA(A_champC_id) VALUES (13);
        ELSE RETURN NULL;
        END CASE;      
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
        RETURN NEW;   
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    ELSIF (TG_OP='UPDATE')THEN
    ---------------------------------------------------------------TABLE_A---------------------------------------------------------------------------------------
        UPDATE aep.troncon
            SET   
                A_date_modif = NEW.A_date_modif,        A_champ1 = NEW.A_champ1,     A_champ2 = NEW.A_champ2,
                A_champ3 = NEW.A_champ3,            A_champ4 = NEW.A_champ4,        A_champ5 = NEW.A_champ5,
                A_champ6 = NEW.A_champ6,            A_champ7 = NEW.A_champ7,     A_champ8 = NEW.A_champ8,
                A_champ9 = NEW.A_champ9,            A_champ10 = NEW.A_champ10,    A_longueur = NEW.A_longueur,
                A_champ11 = NEW.A_champ11,        A_champ12 = NEW.A_champ12,    A_champ13 = NEW.A_champ13
               
            WHERE    
                OLD.A_id = A_id;
    -------------------------------------------------------------A_CHAMP_B_ID---------------------------------------------------------------------------------------
        CASE     WHEN NEW.vue.champB1 = "valeur1" THEN UPDATE tablesA SET A_champB_id=1 WHERE A_champB_id=OLD.A_champB_id;
                WHEN NEW.vue.champB1 = "valeur2" THEN UPDATE tablesA SET A_champB_id=2 WHERE A_champB_id=OLD.A_champB_id;
                 WHEN NEW.vue.champB1 = "valeur3" THEN UPDATE tablesA SET A_champB_id=3 WHERE A_champB_id=OLD.A_champB_id;
                 WHEN NEW.vue.champB1 = "valeur4" THEN UPDATE tablesA SET A_champB_id=4 WHERE A_champB_id=OLD.A_champB_id;
        ELSE RETURN NULL;
        END CASE;
        IF NOT FOUND THEN RETURN NULL;
        ELSE RETURN NEW;
        END IF;
    -------------------------------------------------------A_CHAMP_C_ID---------------------------------------------------------------------------------------
        CASE     WHEN NEW.vue.alias_champC1+champC2= "valeurC1_1-valeurC2_1" THEN UPDATE tablesA SET A_champC_id=1 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_2-valeurC2_2" THEN UPDATE tablesA SET A_champC_id=2 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_3-valeurC2_3" THEN UPDATE tablesA SET A_champC_id=3 WHERE A_champC_id=OLD.A_champC_id;
                WHEN NEW.vue.alias_champC1+champC2= "valeurC1_4-valeurC2_4" THEN UPDATE tablesA SET A_champC_id=4 WHERE A_champC_id=OLD.A_champC_id;
                WHEN NEW.vue.alias_champC1+champC2= "valeurC1_5-valeurC2_5" THEN UPDATE tablesA SET A_champC_id=5 WHERE A_champC_id=OLD.A_champC_id;
                WHEN NEW.vue.alias_champC1+champC2= "valeurC1_6-valeurC2_6" THEN UPDATE tablesA SET A_champC_id=6 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_7-valeurC2_7" THEN UPDATE tablesA SET A_champC_id=7 WHERE A_champC_id=OLD.A_champC_id;
                WHEN NEW.vue.alias_champC1+champC2= "valeurC1_8-valeurC2_8" THEN UPDATE tablesA SET A_champC_id=8 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_9-valeurC2_9" THEN UPDATE tablesA SET A_champC_id=9 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_10-valeurC2_10" THEN UPDATE tablesA SET A_champC_id=10 WHERE A_champC_id=OLD.A_champC_id;
                WHEN NEW.vue.alias_champC1+champC2= "valeurC1_11-valeurC2_11" THEN UPDATE tablesA SET A_champC_id=11 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_12-valeurC2_12" THEN UPDATE tablesA SET A_champC_id=12 WHERE A_champC_id=OLD.A_champC_id;
                 WHEN NEW.vue.alias_champC1+champC2= "valeurC1_13-valeurC2_13" THEN UPDATE tablesA SET A_champC_id=13 WHERE A_champC_id=OLD.A_champC_id;
        ELSE RETURN NULL;
        END CASE;
        IF NOT FOUND THEN RETURN NULL;
        ELSE RETURN NEW;
        END IF;      
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
        RETURN NEW;   
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    ELSIF (TG_OP='DELETE')THEN
    -------------------------------------------------------------------------TABLE_A-----------------------------------------------------------------------------
        DELETE FROM tableA WHERE       
                    A_id= OLD.A_id                           AND    A_date_modif=OLD.A_date_modif    AND    A_champ1=OLD.A_champ1        AND
                    A_champ2=OLD.A_champ2                AND    A_champ3=OLD.A_champ3           AND       A_champ4=OLD.A_champ4         AND
                    A_champ5=OLD.A_champ5                AND    A_champ6=OLD.A_champ6        AND    A_champ7=OLD.A_champ7        AND   
                    A_champ8=OLD.A_champ8                AND    A_champ9=OLD.A_champ9        AND    A_champ10=OLD.A_champ10    AND
                    A_longueur=OLD.A_longueur                AND    A_champ11=OLD.A_champ11    AND    A_champ12=OLD.A_champ12    AND   
                    A_champ13=OLD.A_champ13    ;       
    -------------------------------------------------------------------------A_CHAMP_B_ID----------------------------------------------------------------------------
        DELETE FROM tableA WHERE A_champB_id=OLD.A_champB_id AND A_champB_id =champB_id AND A_id=OLD.A_id;
        IF NOT FOUND THEN RETURN NULL;END IF;

    -----------------------------------------------------------------------A_CHAMP_C_ID------------------------------------------------------------------------
        DELETE FROM tableA WHERE A_champC_id=OLD.A_champC_id AND tA_champC_id =champC_id  AND A_id=OLD.A_id;
        IF NOT FOUND THEN RETURN NULL;END IF;
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
        RETURN NULL;   
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER test_maj
INSTEAD OF INSERT OR UPDATE OR DELETE
ON vue
FOR EACH ROW
EXECUTE PROCEDURE test_maj();

ALTER TABLE objet ENABLE ROW LEVEL SECURITY;
CREATE POLICY test_restriction ON objet TO "utilisateur"
USING (obj_com_insee = (SELECT obj_com_insee FROM objet WHERE obj_com_insee=79000))