Mise à jour de plusieurs tables à l'aide d'un seule vue

c che · 29/07/2016 15:48:09

Bonjour,

J'ai créé une base de données postgres/postgis avec :
TableA champA_id, champA1, champA2, champA_champB_id (clé étrangère), champA_geom

TableB champB_id, champB1

J'ai ensuite créer une vue avec comme définition :
SELECT champA_id, champA1, champA2, champB1, champA_geom
FROM TableA, TableB
WHERE TableA.champA_champB_id = TableB.champB_id

J'aimerai que mes tables soient mises à jour à partir de la vue via QGIS.
J'ai trouvé comment mettre à jour une seule table à partir de la vue (avec une règle).
Mais je suis coincé par les clés étrangères quand ma vue contient plusieurs table puisque je souhaite qu'en fonction de la valeur remplie dans le champB1 ma clé étrangère se remplisse automatiquement avec la valeur correspondante.

Est-ce possible et si oui, comment dois-je procéder?

Je n'ai pas trouvé de solutions sur internet mais peut être n'ai-je pas les bons mots clés.

Merci d'avance pour vos réponses,

c.che

PS: J'ai la version 2.0 de Postgis, 9.5.3 de Postgres et 2.14.1 de Qgis

PPS: J'espère avoir été suffisamment précise mais n'hésitez pas à me faire signe si ce n'est pas le cas

rjuju · 29/07/2016 16:01:03

Un trigger ne pourrait pas résoudre votre problème ? L'exemple le plus proche que j'ai pu trouver dans la doc est : http://docs.postgresql.fr/9.4/plpgsql-t … ry-example

c che · 02/08/2016 10:34:51

J'ai essayé de faire un trigger mais j'ai un problème avec ma fonction (je suis débutante en fonction sql)

Voici ma vue :

CREATE OR REPLACE VIEW aep.canalisation AS

SELECT troncon.tro_id,
materiau.mat_type,
troncon.tro_phi_int,
troncon.tro_phi_ext,
troncon.tro_dn,
pression_n.prn_valeur,
troncon.tro_geom

FROM aep.troncon,
aep.materiau,
aep.pression_n

WHERE troncon.tro_mat_id = materiau.mat_id AND troncon.tro_prn_id = pression_n.prn_id;

et voici la fonction trigger que j ai tenté :

CREATE OR REPLACE FUNCTION vue_del_troncon() RETURNS TRIGGER AS $vue_del_troncon$
BEGIN
IF (TG_OP = 'DELETE') THEN

DELETE FROM aep.troncon WHERE tro_phi_int = OLD.tro_phi_int;
IF NOT FOUND THEN RETURN NULL; END IF;
DELETE FROM aep.troncon WHERE tro_phi_ext = OLD.tro_phi_ext;
IF NOT FOUND THEN RETURN NULL; END IF;
DELETE FROM aep.troncon WHERE tro_dn= OLD.tro_dn;
IF NOT FOUND THEN RETURN NULL; END IF;
DELETE FROM aep.troncon WHERE tro_geom = OLD.tro_geom;
IF NOT FOUND THEN RETURN NULL; END IF;

ON DELETE to canalisation.mat_type DO INSTEAD delete from aep.troncon WHERE tro_mat_id = OLD.tro_mat_id;
ON DELETE to canalisation.prn_valeur DO INSTEAD delete from aep.troncon WHERE tro_prn_id = OLD.tro_prn_id;

END IF;
return null;
END;
$vue_del_troncon$
LANGUAGE plpgsql;

CREATE TRIGGER maj_canalisation
INSTEAD OF INSERT OR UPDATE OR DELETE
ON aep.canalisation
FOR EACH ROW
EXECUTE PROCEDURE vue_del_troncon();

Mon problème vient de ON DELETE DO INSTEAD qui apparemment n'est fait que pour les règles, mais, n'y a t-il pas un équivalent pour les fonctions ?
Ou bien suis-je partie sur une mauvaise piste?

Dernière modification par c che (02/08/2016 15:51:01)

rjuju · 02/08/2016 19:57:10

Que cherchez-vous à faire exactement ?

c che · 03/08/2016 12:17:02

Je souhaite que les utilisateurs, à partir de la vue, puissent mettre à jour la table tronçon (ou d'autres par la suite). Le problème c'est que, au niveau des clés étrangères, il faut rentrer des chiffres qui correspondent aux différentes entrées dans les tables annexes, mais les utilisateurs ne savent pas directement quel chiffre correspond à quel entrée.

Par exemple, pour une table annexe matériau, il faut qu'ils aient juste à choisir l'élément "PVC" et non le chiffre 12 contenu dans la clé étrangère lorsqu'ils mettent à jour la table tronçon,
mais que ce soit bien l'entrée 12 qui s'inscrive dans la colonne de la clé étrangère dans ma table tronçon.

c.che
PS: J'ai la version 2.0 de Postgis, 9.5.3 de Postgres et 2.14.1 de Qgis
PPS: J'espère avoir été suffisamment précise mais n'hésitez pas à me faire signe si ce n'est pas le cas

Dernière modification par c che (03/08/2016 12:17:38)

gleu · 03/08/2016 17:33:38

Normalement, c''est à l'application de gérer ce genre de détails. Elle affiche à l'utilisateur la liste des matériaux et suivant le matériau choisi, elle construit la requête d'insertion dans la table.

Si jamais l'utilisateur écrit lui-même la requête, passer par une vue ne changera pas le problème.

c che · 04/08/2016 08:57:10

Par application parlez-vous d'un trigger ou bien de QGIS/Postgis/Postgres ? Comment doit on la paramétrer pour qu'elle le fasse ?

c.che

gleu · 04/08/2016 12:01:22

Je parle de QGIS. Ne connaissant pas l'application, je ne pourrais pas expliquer comment la paramétrer.

c che · 04/08/2016 14:18:32

N'y a-t-il pas un moyen de gérer ça avec postgres? Ce serait plus pratique pour nous, notamment quand nous devrons passer en Websig.

c che · 18/08/2016 14:23:15

J'ai testé sur Qgis mais il y a un problème, il me remplit un champ integer avec du texte donc ça ne fonctionne pas.
De plus, même si la fonction fonctionnait, il faudrait refaire les manipulations pour chaque champ dès que l'on change de projet/document.

N'y a t il pas un moyen en passant par une fonction puis un trigger pour automatiser tout ça?

Dernière modification par c che (18/08/2016 14:26:56)

c che · 22/08/2016 17:15:33

Bonjour, mon code SQL ne fonctionne pas, pouvez vous me dire ce qui cloche?

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();

rjuju · 22/08/2016 17:36:45

Je pense sincèrement que personne ne va lire la centaine de ligne de votre trigger sans même savoir quel est le problème.

gleu · 22/08/2016 21:08:34

Je confirme

c che · 23/08/2016 08:57:50

Oups! Désolée j'ai oublié de le préciser :s

En fait lorsque j'ouvre ma table contenant la géométrie dans QGIS et que j'essaie de la mettre à jour, au moment de l enregistrement il m'affiche :

Impossible de valider les changements pour la couche vue
Erreurs : ERREUR : 1 attribut non-ajouté.

Erreur du fournisseur de données :
Erreur PostGIS lors de l'ajout d'entité : ERREUR: une valeur NULL viole la contrainte NOT NULL de la colonne «A_CHAMP_B_ID »
DETAIL: La ligne en échec contient (11116, 2016-08-23, null, null, null, null, null, null, null, null, null, null, 939.1165, null, null, null, 01050000206B0F000001000000010200000003000000B103684234C63541EFDE..., 79000, null, null, null, null, null, 1, null, null, null)

En gros, seuls mes champs automatiques et la géométrie sont remplis

gleu · 23/08/2016 09:11:02

Ne connaissant pas la structure de la table, ça va être difficile de dire quelque chose de bien concret. Je peux déjà dire que mélanger rules et triggers est une idée catastrophique. Abandonner les rules, ne faites que des triggers.

c che · 23/08/2016 21:50:05

Voici la structure de la table avec la table A qui est ma table principale, dans la vue on retrouve tous les champs présents dans A en plus des champs B1 et C

---------------------------------------------------------------------
TABLE_A
---------------------------------------------------------------------
pk A_id    serial NOT NULL,
A_date_modif    date DEFAULT ('now'::text)::date,
A_champ1    character varying(20),
A_champ2    character varying(250),
A_champ3    boolean,
A_champ4    numeric(4,0),
A_champ5    boolean,
A_champ6    numeric(8,4),
A_champ7    numeric(8,4),
A_champ8    numeric(4,0),
A_champ9    boolean,
A_champ10    character varying(50),
A_longueur    numeric(9,4) DEFAULT length('((-1,0),(1,0))'::path),
A_champ11    character varying(100),
A_champ12    character varying(250),
A_champ13    character varying(250),
fk A_champB_id integer NOT NULL,
fk A_champC_id integer NOT NULL,

---------------------------------------------------------------------
TABLE_B
---------------------------------------------------------------------
pk champB_id serial NOT NULL,
champB1 character varying(50)

---------------------------------------------------------------------
TABLE_C
---------------------------------------------------------------------
pk champC_id    serial NOT NULL,
champC1    character varying(50),
champC2    character varying(50)

---------------------------------------------------------------------
VUE
---------------------------------------------------------------------
SELECT
A_id    ,
A_date_modif ,
A_champ1 ,
champB1    ,
champC1||'-'|| champC2 AS champ_concatener ,
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

FROM
TABLE A
TABLE B
TABLE C

WHERE
A_champB_id = champB_id AND
A_champC_id = champC_id

J'utilise les rules pour pouvoir réussir à mettre à jour le champ contenant la géométrie mais je peux peut etre effectivement les intégrer à ma fonction trigger. Pour le coup mes rules fonctionnent (je les ai testé sur une autre vue) c'est au niveau du trigger que ça cloche

Dernière modification par c che (23/08/2016 21:57:30)

gleu · 24/08/2016 09:00:22

Vu la définition de table_a, je ne vois pas en quoi vous êtes étonné qu'il rouspète d'avoir une valeur NULL pour la colonne A_champB_id alors que cette colonne n'est pas indiquée dans l'INSERT et qu'elle est déclarée NOT NULL.

c che · 25/08/2016 09:05:14

Apès avoir modifié l'INSERT pour qu'il contienne tout mes champs j ai ce nouveau message d'erreur

l'enregistrement « new » n'a pas de champs « A_id »

Si j'enleve ce champ de mon insert il me dit la meme chose mais pour le champ suivant
je ne comprends pas pourquoi il m'affiche ça. Faut-il déclarer New? ou bien cette variable n est pas applicable pour un insert (dans ce cas,par quoi faut il le remplacer)?

Dernière modification par c che (25/08/2016 09:07:49)

gleu · 25/08/2016 09:21:09

Si vous avez mis des majuscules dans le nom de vos objets, vous devez mettre les noms des objets en question entre guillemets doubles.

#1 29/07/2016 15:48:09

Mise à jour de plusieurs tables à l'aide d'un seule vue

#2 29/07/2016 16:01:03

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#3 02/08/2016 10:34:51

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#4 02/08/2016 19:57:10

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#5 03/08/2016 12:17:02

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#6 03/08/2016 17:33:38

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#7 04/08/2016 08:57:10

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#8 04/08/2016 12:01:22

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#9 04/08/2016 14:18:32

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#10 18/08/2016 14:23:15

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#11 22/08/2016 17:15:33

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#12 22/08/2016 17:36:45

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#13 22/08/2016 21:08:34

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#14 23/08/2016 08:57:50

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#15 23/08/2016 09:11:02

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#16 23/08/2016 21:50:05

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#17 24/08/2016 09:00:22

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#18 25/08/2016 09:05:14

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

#19 25/08/2016 09:21:09

Re : Mise à jour de plusieurs tables à l'aide d'un seule vue

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux