Annonce
Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).
En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.
#1 Mon 26 February 2018 16:20
- bruhnild
- Participant actif
- Lieu: Lyon
- Date d'inscription: 7 Jun 2014
- Messages: 130
Update de table dans une fonction trigger [postgres]
Bonjour,
Je voudrais créer un trigger pour mettre à jour l'id_opp de la table numerisation_test dès qu'il y a une insertion ou une mise à jour dans la table.
Voici ma fonction:
Code:
CREATE OR REPLACE FUNCTION update_id_opp() RETURNS TRIGGER AS $$ BEGIN UPDATE coordination.numerisation_test SET id_opp=subquery.id_opp FROM ( SELECT 'OPP_'||pr ||'_'|| nro_ref||'_'||lpad(CAST(row_number() OVER (PARTITION BY nro_ref, nbr_doublon ORDER BY id) + nbr_doublon - 1 AS VARCHAR), 3, '0')id_opp FROM ( WITH sequenc AS ( SELECT a.id as id, a.geom, b.nro_ref, c.nbr_doublon +1 as nbr_doublon, CASE WHEN b.lot = 1 and b.pr IS NULL THEN '1-XX'::character varying WHEN b.lot = 2 and b.pr IS NULL THEN '2-XX'::character varying WHEN b.lot = 3 and b.pr IS NULL THEN '3-XX'::character varying WHEN b.lot = 4 and b.pr IS NULL THEN '4-XX'::character varying ELSE b.pr END AS pr FROM coordination.numerisation as a JOIN administratif.communes as b on a.com_dep=b.commune JOIN coordination.vue_doublons_nro_all as c USING (nro_ref)) SELECT * FROM sequenc) AS concat) AS subquery; RETURN NULL; END; $$ LANGUAGE 'plpgsql';
Et mon trigger:
Code:
DROP TRIGGER IF EXISTS trg_update_id_opp ON coordination.numerisation_test; CREATE TRIGGER trg_update_id_opp BEFORE INSERT OR UPDATE ON coordination.numerisation_test FOR EACH ROW EXECUTE PROCEDURE update_id_opp();
Lorsque j'essaye d'enregistrer une création d'entité dans Qgis, je reçois cette erreur là :
"Impossible de valider les changements pour la couche numerisation_test
Erreurs : ERREUR : 1 attribut non-ajouté.
Erreur du fournisseur de données :
Erreur PostGIS lors de l'ajout d'entité : ERREUR: dépassement de limite (en profondeur) de la pile
HINT: Augmenter le paramètre « max_stack_depth » (actuellement 2048 Ko) après vous
être assuré que la limite de profondeur de la pile de la plateforme est
adéquate.
CONTEXT: instruction SQL « UPDATE coordination.numerisation_test
SET id_opp=subquery.id_opp
"
Je crois quele résultat donne une boucle infinie puisque j'essaye de faire un update de la table coordination.numerisation_test qui déclenche le trigger, qui exécute une fonction qui fait un UPDATE sur coordination.numerisation_test qui déclenche le trigger etc..
Je ne vois pas comment modifier ma fonction trigger pour autoincrémenter l'id_opp. Merci d'avance pour votre aide.
Hors ligne
#2 Tue 27 February 2018 09:43
- JP LLORENS
- Participant assidu
- Date d'inscription: 12 Nov 2008
- Messages: 231
Re: Update de table dans une fonction trigger [postgres]
Bonjour.
Si le trigger boucle sur lui même et que votre objectif est de mettre à jour uniquement l'id_opp, alors en spécifiant les champs sur lesquels le trigger se déclenche (BEFORE INSERT OR UPDATE OF XXXXX, YYYYY, NNNNN, ....ON coordination.numerisation_test) (en excluant id_opp) alors le problème de boucle devrait se régler je suppose.
JP
Hors ligne
#3 Tue 27 February 2018 10:13
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1129
Re: Update de table dans une fonction trigger [postgres]
Un trigger pour calculer un champ après modification est souvent considéré comme une mauvaise pratique.
Je vous conseille de passer par une vue qui vous rendra la table souhaitée + le champ doublon calculé.
Code:
CREATE VIEW vue_numerisation_doublon AS ( SELECT numerisation_test.*, 'OPP_'||pr ||'_'|| nro_ref||'_'|| lpad(row_number() OVER (PARTITION BY nro_ref, com_dep ORDER BY id)::text, 3, '0') id_opp FROM coordination.numerisation_test );
Si vous voulez mettre à jour cette vue dans qgis, vous pouvez mettre en place des triggers qui redirigeront
les mises à jour vers la table grâce à l’évènement INSTEAD OF.
Dernière modification par tumasgiu (Tue 27 February 2018 10:15)
Hors ligne
#4 Tue 27 February 2018 10:16
- bruhnild
- Participant actif
- Lieu: Lyon
- Date d'inscription: 7 Jun 2014
- Messages: 130
Re: Update de table dans une fonction trigger [postgres]
Merci JP LORRENS pour la réponse, bonne nouvelle j'ai rajouté tous les champs de la table coordination.numerisation_test excepté id_opp et je n'ai plus l'erreur à la création d'entités!
Code:
DROP TRIGGER IF EXISTS trg_update_id_opp ON coordination.numerisation_test; CREATE TRIGGER trg_update_id_opp AFTER INSERT OR UPDATE OF geom, lot, id_prog, id_nro, id_reseau, insee, com_dep, statut, phase, emprise, nom, support, travaux, typ_reseau, longueur, debut_trvx, prog_dsp, moa, cdd, commentair, envoi_moe, date, id ON coordination.numerisation_test FOR EACH ROW EXECUTE PROCEDURE update_id_opp();
L'id_opp s'écrit bien mais il est le même partout(voir screenshot de la table attributaire en pièce jointe).
Je pense que l'erreur vient de l'update dans la fonction update_id_opp. Pourtant lorsque je lance le select indépendamment de la fonction, il autoincrémente un id_opp différent à chaque ligne..
Hors ligne
#5 Tue 27 February 2018 11:13
- JP LLORENS
- Participant assidu
- Date d'inscription: 12 Nov 2008
- Messages: 231
Re: Update de table dans une fonction trigger [postgres]
Si la table qui déclenche le trigger est la même que celle sur laquelle s'applique le trigger alors inutile dans votre fonction de mettreUPDATE coordination.numerisation_test SET id_opp=subquery.id_opp; Il faut utiliser directement la ligne mise à jourNEW.id_opp =subquery.id_opp .
Seule la ligne active va ainsi récupérer la nouvelle valeur de id_opp, contrairement à votre requête dans laquelle toutes les lignes (il n'y a pas de where clause !) sont mises à jour; Je vous renvoie vers la doc pour les notions de NEW / OLD
D'autre part, dans la création de votre trigger, vous référencez tous les attributs de la table (à l’exception de id_opp bien sûr) => seuls les attributs susceptibles de déclencher le trigger lors d'une modification doivent apparaître dans le create trigger ...of ...
JP
Hors ligne
#6 Tue 27 February 2018 16:40
- bruhnild
- Participant actif
- Lieu: Lyon
- Date d'inscription: 7 Jun 2014
- Messages: 130
Re: Update de table dans une fonction trigger [postgres]
Merci pour vos réponses!
A force de persévérance j'ai pu obtenir le résultat souhaité!
Code:
CREATE OR REPLACE FUNCTION update_id_opp() RETURNS TRIGGER AS $$ BEGIN NEW.id_opp := concat('OPP_', -- cherche id prog sinon lot (CASE WHEN NEW.lot like '1' and NEW.id_prog = 'NULL' THEN '1-XX'::character varying WHEN NEW.lot like '2' and NEW.id_prog = 'NULL' THEN '2-XX'::character varying WHEN NEW.lot like '3' and NEW.id_prog = 'NULL' THEN '3-XX'::character varying WHEN NEW.lot like '4' and NEW.id_prog = 'NULL' THEN '4-XX'::character varying ELSE NEW.id_prog END), '_', -- cherche nro ref NEW.id_nro, '_', -- incrémente en fonction du nombre de fois que l'id_nro a été utilisé lpad((SELECT cast(nbr_doublon+1 as varchar) test FROM coordination.vue_doublons_nro_all where nro_ref like NEW.id_nro),3,'0')) as id_opp ; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS trg_update_id_opp ON coordination.numerisation; CREATE TRIGGER trg_update_id_opp BEFORE INSERT OR UPDATE OF geom ON coordination.numerisation_test FOR EACH ROW EXECUTE PROCEDURE update_id_opp();
Hors ligne