Le portail francophone de la géomatique

bruhnild · Mon 26 February 2018 16:20

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.

JP LLORENS · Tue 27 February 2018 09:43

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

tumasgiu · Tue 27 February 2018 10:13

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)

bruhnild · Tue 27 February 2018 10:16

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..

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

JP LLORENS · Tue 27 February 2018 11:13

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

bruhnild · Tue 27 February 2018 16:40

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

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Mon 26 February 2018 16:20

Update de table dans une fonction trigger [postgres]

Code:

Code:

#2 Tue 27 February 2018 09:43

Re: Update de table dans une fonction trigger [postgres]

#3 Tue 27 February 2018 10:13

Re: Update de table dans une fonction trigger [postgres]

Code:

#4 Tue 27 February 2018 10:16

Re: Update de table dans une fonction trigger [postgres]

Code:

#5 Tue 27 February 2018 11:13

Re: Update de table dans une fonction trigger [postgres]

#6 Tue 27 February 2018 16:40

Re: Update de table dans une fonction trigger [postgres]

Code:

Pied de page des forums