Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

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


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

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

 

Pied de page des forums

Powered by FluxBB