Discussion :

[fsmrel]

Bonsoir Loïc,

Oui cette contrainte est intéressante. J'aimerais également en ajouter une qui oblige à ce que la distance cumulée (dans la table support) d'un support n + 1 soit forcément supérieure à la distance cumulée d'un support n.

C’est l’attribut Numero de la table SUPPORT qui permet de savoir qu’il s’agit du n^ième support de la ligne définie par l’attribut LigneId de cette table ?

Je ne comprends pas vraiment ce trigger que vous avez écrit pour s'assurer que les supports de départ et d’arrivée d’un câble appartiennent bien à la même ligne...

Pour illustrer ce qui suit, je commence par déclarer une structure simplifiée de la table CABLE :

CREATE TABLE CABLE
(
        CableId             SERIAL
      , CableCode           CHAR(5)            NOT NULL        
      , SupportDepartId     INTEGER            NOT NULL
      , SupportArriveeId    INTEGER            NOT NULL
    , CONSTRAINT CABLE_PK PRIMARY KEY (CableId)
    , CONSTRAINT CABLE_AK UNIQUE (CableCode)    
    , CONSTRAINT CABLE_SUPPORT_DEPART_FK FOREIGN KEY (SupportDepartId)  
          REFERENCES SUPPORT (SupportId)
    , CONSTRAINT CABLE_SUPPORT_ARRIVEE_FK FOREIGN KEY (SupportArriveeId)  
          REFERENCES SUPPORT (Supportid)
    , CONSTRAINT CABLE_SUPPORT_BOUCLE CHECK (SupportDepartId <> SupportArriveeId)
) ;

Je réécris le trigger selon les conventions de PostgreSQL, lequel demande que l’on crée d’abord une procédure de type TRIGGER. Cela dit, le corps de la procédure ressemble étrangement à celui du trigger MySQL, et reste donc normalement aussi glauque...

CREATE FUNCTION CABLE_LIGNE_COHERENCE_FN()
      RETURNS TRIGGER AS
$le_trigger$ 
    DECLARE Erreur   VARCHAR  ;
    BEGIN
        Erreur = '' ;
        IF 1 < (
                SELECT COUNT(*) 
                FROM (SELECT x.LigneId
                      FROM   SUPPORT AS x INNER JOIN LIGNE AS y ON x.LigneId = y.LigneId
                      WHERE  NEW.SupportDepartId = x.SupportId
                      UNION
                      SELECT x.ligneid
                      FROM   SUPPORT AS x INNER JOIN LIGNE AS y ON x.LigneId = y.LigneId
                      WHERE  NEW.SupportArriveeId = x.SupportId
                     ) AS theTable
               )  
            THEN 
              Erreur = 'CableCode = '''|| NEW.CableCode || 
                  ''' : La ligne du support de départ est différente de celle du support d''arrivée.' ;
             RAISE EXCEPTION SQLSTATE '45001' USING MESSAGE = Erreur ;
        END IF ;
        RETURN NEW ;
    END ;
  $le_trigger$ 
  LANGUAGE plpgsql ;

CREATE TRIGGER CABLE_LIGNE_COHERENCE_TR BEFORE INSERT OR UPDATE ON CABLE
    FOR EACH ROW EXECUTE PROCEDURE CABLE_LIGNE_COHERENCE_FN();

En relationnel pur, avec un langage orienté D, le code que j’ai proposé peut être réécrit de façon élégante et plus dense, et il est probable qu’avec PostgreSQL on puisse aussi alléger le code que j’ai proposé. Quoi qu’il en soit, l’idée est la suivante :

(1) Déterminer la ligne du support de départ du câble en cours :

SELECT x.LigneId
FROM   SUPPORT AS x INNER JOIN LIGNE AS y ON x.LigneId = y.LigneId
WHERE  NEW.SupportDepartId = x.SupportId

Où la lettre « x » est un alias, un synonyme, un token, pour SUPPORT. A défaut, j’aurais pu écrire :

SELECT SUPPORT.LigneId
FROM   SUPPORT INNER JOIN LIGNE ON SUPPORT.LigneId = LIGNE.LigneId
WHERE  NEW.SupportDepartId = SUPPORT.SupportId
 

Mais certainement pas :

SELECT LigneId
FROM   SUPPORT INNER JOIN LIGNE ON SUPPORT.LigneId = LIGNE.LigneId
WHERE  NEW.SupportDepartId = SupportId

Car dans « SELECT LigneId », il y a une ambiguïté : A quelle table fait référence l’attribut LigneId : SUPPORT ? LIGNE ?

Par contre, dans la clause WHERE, l’attribut SupportId n’étant présent que dans l’en-tête de la table SUPPORT, on peut faire l’économie de l’alias.

Dans la clause FROM, vous aurez reconnu la jointure des tables SUPPORT et LIGNE. Comme vous ne vous servez pas d’ACCESS, vous pouvez faire l’économie du mot-clé « INNER » :

FROM SUPPORT JOIN LIGNE ON x.LigneId = y.LigneId

Pour varier les plaisirs, vous pouvez aussi écrire :

SELECT SUPPORT.LigneId
FROM   SUPPORT NATURAL JOIN LIGNE
WHERE  NEW.SupportDepartId = SupportId
 

Etc. Mais le plus sage est d’en rester à « FROM SUPPORT JOIN LIGNE ON x.LigneId = y.LigneId », car « NATURAL JOIN*» est piégeux si on n’est pas vigilant : par exemple si on a deux attributs ayant le même nom (Id, libellé, Date, ...) dans deux tables à joindre, mais n’ayant rien de commun, sinon une homonymie accidentelle, NATURAL JOIN les fera participer illégitimement mais légalement à la jointure.

Passons à la clause WHERE :

WHERE NEW.SupportDepartId = SupportId

NEW est une variable qui contient la valeur d’une ligne à ajouter dans la table CABLE (laquelle fait l’objet du trigger) à l’occasion d’un INSERT (ou la nouvelle valeur dans le cas d’un UPDATE). L’en-tête de la ligne NEW est celui de la table CABLE, et on a accès à chaque attribut correspondant : CableId, CableCode, SupportDepartId, SupportArriveeId.

Si donc je code :

INSERT INTO CABLE (CableCode, SupportDepartId, SupportArriveeId) VALUES ('C0001', 1, 2) ;

Du fait de l’utilisation du type SERIAL, CableId aura la valeur calculée par PostgreSQL, par exemple 1.

Les attributs de la variable NEW prennent les valeurs suivantes :

CableId = 1 ;

CableCode = 'C0001' ;

SupportDepartId = 1 ;

SupportArriveeId = 2.

Ainsi donc, au moyen de la clause WHERE, on traite du support de départ du câble à créer : SupportDepartId = 1.

Le résultat du SELECT est le suivant : LigneId = 1.


(2) Déterminer la ligne du support d’arrivée du câble en cours :

On a un SELECT identique à celui qu’on vient de décortiquer, mais au moyen de la clause WHERE, on traite du support d’arrivée du câble à créer : SupportArriveeId = 2.

Le résultat du SELECT est le suivant : LigneId = 2.

SELECT x.LigneId
FROM   SUPPORT AS x INNER JOIN LIGNE AS y ON x.LigneId = y.LigneId
WHERE  NEW.SupportDepartId = x.SupportId

A cette occasion, je me rends compte que j’ai eu un copier/coller malheureux dans mes précédents messages en ce qui concerne cette partie du trigger : j’y ai effectué les corrections.


(3) chacun des SELECT précédents respecte le principe de fermeture algébrique : le résultat de chaque SELECT est une table et de l’union de ces tables naîtra une table, donc de même nature que ses parents.

Le 1er SELECT produit la table :

LigneId
-------
      1

Le 2e SELECT produit la table :

LigneId
-------
      2

Et leur union produit la table :

LigneId
-------
      1
      2

A cette union j’ai donné un nom dans la procédure : « theTable ».

Puisque c’est une table, je peux lui appliquer les opérations et fonctions de l’algèbre relationnelle, par exemple la fonction COUNT, pour savoir combien de lignes compte cette table. En l’occurrence, COUNT(*) = 2. Cela veut dire que pour le câble en cours, la ligne du support de départ est différente de la ligne du support d’arrivée.


Si maintenant je code :

INSERT INTO CABLE (CableCode, SupportDepartId, SupportArriveeId) VALUES ('C0003', 3, 4) ;

Le support de départ (3) et le support d’arrivée (4) font référence à la même ligne (3) :

Le 1er SELECT produit la table :

LigneId
-------
      3

Le 2e SELECT produit la table :

LigneId
-------
      3

Et leur union produit la table :

LigneId
-------
      3

En effet, UNION étant un opérateur ensembliste, il élimine les doublons. Cette fois-ci, COUNT(*) = 1 : Le support de départ et le support d’arrivée font référence à la même ligne.


Peut-être avez-vous aussi peut-être été troublé par la formulation :

1 < (SELECT COUNT(*)...) ;

Mais vous pouvez bien sûr écrire :

(SELECT COUNT(*)...) > 1 ;

[LoicL89]

Bonsoir fsmrel,

C’est l’attribut Numero de la table SUPPORT qui permet de savoir qu’il s’agit du nième support de la ligne définie par l’attribut LigneId de cette table ?

Oui c'est bien ça.

D'accord pour le trigger, c'est déjà beaucoup plus clair ! Seuls 2 - 3 éléments me laissent perplexe encore :

Le "Erreur" juste après le BEGIN? Pourquoi vient-il là?

Erreur = '' ;

Et que signifie :

RAISE EXCEPTION SQLSTATE '45001' USING MESSAGE = Erreur ;

Merci,

Cordialement,

Loic

[fsmrel]

Bonsoir Loïc,

Le "Erreur" juste après le BEGIN? Pourquoi vient-il là?

Il vient là parce que c’est une scorie de tests précédents, vous pouvez supprimer cette instruction.

Et que signifie*:
RAISE EXCEPTION SQLSTATE '45001' USING MESSAGE = Erreur ;

Cela correspond à la gestion des erreurs, telle qu’elle est définie par PostgreSQL. SQLSTATE est un mot-clé de la norme SQL, donc connu de tous les SGBDR. Dans '45001', les deux 1ers caractères représentent la classe de l’erreur : la classe 45 étant inutilisée par PostgreSQL, je m’en suis servi comme synonyme de « Erreur détectée par Loïc, voyez icelui pour plus ample informé », mais vous pouvez utiliser toute classe libre et numéroter à votre guise...



Je suis en train de regarder le problème de la distance cumulée : a priori ça n’est pas insurmontable, je vous tiens au courant.

[fsmrel]

Bonsoir à nouveau Loïc,


Et voilà un trigger pour contrôler les distances cumulées...

Pour le moment, je traite des inserts, je regarderai les updates si je peux, mais il faut que je souffle... Je ne sais pas si ce qui suit répond exactement à ce que vous recherchez. En tout cas, vous voilà alpha-testeur ^^


Création des tables

SET SCHEMA 'temp' ; 

DROP FUNCTION IF EXISTS SUPPORT_DISTANCE_COHERENCE_INSERT_FN() CASCADE ;
DROP TABLE IF EXISTS SUPPORT CASCADE ;
DROP TABLE IF EXISTS LIGNE ;
DROP TABLE IF EXISTS COMMUNE ;

CREATE TABLE COMMUNE
(
        CommuneId            SERIAL
      , CommuneInsee         INTEGER                 NOT NULL
      , CommuneNom           VARCHAR(32)             NOT NULL
    , CONSTRAINT COMMUNE_PK PRIMARY KEY (CommuneId)
    , CONSTRAINT COMMUNE_AK UNIQUE (CommuneInsee) 
) ;

CREATE TABLE LIGNE 
(
        LigneId                  SERIAL
      , LigneNom                 VARCHAR(32)            NOT NULL
      , Tension                  INTEGER                NOT NULL
    , CONSTRAINT LIGNE_PK PRIMARY KEY (LigneId) 
) ;

CREATE TABLE SUPPORT 
(
        SupportId                SERIAL
      , SupportNo                INTEGER                NOT NULL
      , DistanceCumul            INTEGER                NOT NULL 
      , SensParcours             VARCHAR(4)             NOT NULL  DEFAULT ''
      , CommuneId                INTEGER                NOT NULL
      , LigneId                  INTEGER                NOT NULL
    , CONSTRAINT SUPPORT_PK PRIMARY KEY (SupportId) 
    , CONSTRAINT SUPPORT_NO_AK UNIQUE (SupportNo) 
    , CONSTRAINT SUPPORT_COMMUNE_FK FOREIGN KEY (CommuneId)  
          REFERENCES COMMUNE (CommuneId)  
    , CONSTRAINT SUPPORT_LIGNE_FK FOREIGN KEY (LigneId)  
          REFERENCES LIGNE (LigneId) 
) ;

Un p’tit trigger des familles (améliorable)...

Les distances cumulées des supports de la ligne 1 vont sans le sens croissant ;
Les distances cumulées des supports de la ligne 4 vont sans le sens décroissant.

J’ai laissé les « RAISE INFO » à des fins de débogage, au cas où...

CREATE FUNCTION SUPPORT_DISTANCE_COHERENCE_INSERT_FN()
      RETURNS TRIGGER AS
$le_trigger$ 
    DECLARE Erreur                       VARCHAR ;
    DECLARE theKount                     INTEGER ;
    DECLARE theCumulDistanceAntecedent   INTEGER ; 
    DECLARE theCumulDistanceSuccesseur   INTEGER ; 
    DECLARE thePredecesseur              INTEGER ; 
    DECLARE theSuccesseur                INTEGER ; 
    DECLARE theSupportDebut              INTEGER ; 
    DECLARE theSupportAcreer             INTEGER ; 
    DECLARE theSensParcours              VARCHAR ;
     
    BEGIN
        Erreur = '' ;
        RAISE INFO  USING MESSAGE = TG_OP || ' -------- nouvel ' || TG_OP || ' -------------' ;
       
-- --------------------------------------------------------------------------------------------
-- On compte le nombre de supports déjà existants pour la ligne concernée  
-- --------------------------------------------------------------------------------------------

        theKount = (SELECT COUNT(*) FROM SUPPORT WHERE LigneId = NEW.LigneId) ;
        RAISE INFO  USING MESSAGE = TG_OP || ', theKount = ' || theKount ;
        
-- --------------------------------------------------------------------------------------------
-- On commence par traiter le cas où la table ne contient aucun support pour la ligne concernée :
-- Cézigue ajoute un 1er support. Partons du principe que sa distance cumulée doit être égale à 0.
-- Si c'est le cas, comme on ne sait pas encore dans quel sens parcourir les supports (mode 
-- ascendant ou descendant, on considère que ce sens est pour le moment inconnu : SensParcours = '?'   
-- ----------------------------------------------------------------------------------------- 

        IF theKount = 0 THEN
            IF NEW.DistanceCumul > 0 THEN
                 Erreur = 'SupportNo = '''|| NEW.SupportNo 
                          || '''  est le 1er support créé pour la ligne ''' 
                          || NEW.LigneId || ''' et doit donc être situé à la distance 0.' ;
                 RAISE EXCEPTION SQLSTATE '45001' USING MESSAGE = Erreur ;
            ELSE
                New.SensParcours = '?' ;
                RAISE INFO  USING MESSAGE = TG_OP || ' NEW.SupportNo = ''' 
                                            || NEW.SupportNo || ''' ; New.DistanceCumul = '''  
                                            || New.DistanceCumul || '''.' ;
                RETURN NEW ;         
            END IF ; 
        END IF ;

        ---------------------------------------------------------------------------------------
        -- 2e support et suivants : 
           Détermination du prédécesseur (ou successeur) sur la ligne du support
        ---------------------------------------------------------------------------------------       

        --------------------------------------------------------------------
        -- Y a-t-il un prédécesseur pour le support en cours ? 
        --------------------------------------------------------------------
        thePredecesseur = (SELECT SupportNo 
                           FROM   SUPPORT 
                           WHERE  SupportNo = (SELECT MAX(SupportNo)
                                               FROM   SUPPORT
                                               WHERE  SupportNo < NEW.SupportNo
                                                 AND  SensParcours <> 'desc'    -- sinon on interpréterait un 'desc' comme un 'asc'
                                                 AND  LigneId = New.LigneId)) ;

        RAISE INFO  USING MESSAGE = TG_OP || ', NEW.SupportNo = ' || NEW.SupportNo ;                                                                                                 
        RAISE INFO  USING MESSAGE = TG_OP || ', thePredecesseur (MAX) = ' 
                                          || coalesce(thePredecesseur, -1) ;

        --------------------------------------------------------------------------
        -- S'il n'y a de prédécesseur, c'est donc qu'il s'agit du successeur 
        --------------------------------------------------------------------------
 
        IF COALESCE(thePredecesseur, -1) = -1 THEN 
             thePredecesseur = (SELECT SupportNo 
                           FROM   SUPPORT 
                           WHERE  SupportNo = (SELECT MIN(SupportNo)
                                               FROM   SUPPORT
                                               WHERE  SupportNo > NEW.SupportNo
                                                 AND  LigneId = New.LigneId)) ;
                                                 
            RAISE INFO  USING MESSAGE = TG_OP || ', thePredecesseur (MIN) = ' 
                                              || coalesce(thePredecesseur, -1) ;                                                 
        END IF ;
        
        theCumulDistanceAntecedent = (SELECT DistanceCumul 
                                      FROM   SUPPORT 
                                      WHERE  SupportNo = thePredecesseur) ;
                                      
       RAISE INFO  USING MESSAGE = TG_OP || ', theCumulDistanceAntecedent = ' 
                                         || theCumulDistanceAntecedent ;

   -- ------------------------------------------------------------------------------------------
   -- On traite du cas où l'on n'a précédemment créé qu'un seul support pour la ligne en cours 
   -- et où il s'agit d'en insérer un 2e. C'est à ce moment-là qu'on décide du sens de
   -- parcours de supports : ascendant ou descendant. 
   -- ------------------------------------------------------------------------------------------ 
  
        IF theKount = 1 THEN    
            theSupportDebut = (SELECT SupportNo 
                               FROM   SUPPORT 
                               WHERE  LigneId = New.LigneId) ;

            RAISE INFO  USING MESSAGE = TG_OP || ', New.LigneId = ''' || New.LigneId || ''', theSupportDebut = ' || theSupportDebut ;
        
            theSupportAcreer = New.SupportNo ; 
            RAISE INFO  USING MESSAGE = TG_OP || ', theSupportAcreer = ' || theSupportAcreer ;
            
            IF theSupportAcreer > theSupportDebut 
            AND New.DistanceCumul > theCumulDistanceAntecedent THEN
                New.SensParcours = 'asc' ;    
            ELSEIF theSupportAcreer < theSupportDebut 
            AND New.DistanceCumul > theCumulDistanceAntecedent  THEN 
                New.SensParcours = 'desc' ;  
            ELSE
                Erreur = 'La distance cumulée ''' || New.DistanceCumul || ''' du support '''
                         || NEW.SupportNo 
                         || ''' doit être différente de la distance cumulée  ''' 
                         || theCumulDistanceAntecedent 
                         || ''' du support précédent ''' || thePredecesseur || ''' !' ;
                RAISE EXCEPTION SQLSTATE '45002' USING MESSAGE = Erreur ;                   
            END IF ;
     
            RAISE INFO  USING MESSAGE = TG_OP || ', SensParcours qu''on vient de déterminer : ''' 
                                               ||  New.SensParcours || '''.' ;
             
        -- ------------------------------------------------------------------------------------
        -- On met à jour le sens du parcours pour le support de début, qui jusque là valait '?',
        -- on ajoute le support et on dégage.
        -- -------------------------------------------------------------------------------------
            UPDATE SUPPORT 
                SET SensParcours = New.SensParcours  
                WHERE LigneId = New.LigneId and SensParcours = '?' ;
            RETURN NEW ;                
        END IF ;
        
   -----------------------------------------------------------------------------------
   -- On traite des supports à partir du 3e de la ligne en cours
   -----------------------------------------------------------------------------------
    
        New.SensParcours = (SELECT SensParcours 
                            FROM   SUPPORT 
                            WHERE  LigneId = New.LigneId AND DistanceCumul = 0) ;                          
                            
        RAISE INFO  USING MESSAGE = 'New.SensParcours = ' || New.SensParcours ;
        
        theCumulDistanceAntecedent = (SELECT DistanceCumul 
                                      FROM   SUPPORT 
                                      WHERE  SupportNo = thePredecesseur) ;
                                      
        RAISE INFO  USING MESSAGE = 'NEW.SupportNo = ' || NEW.SupportNo 
                           || ', New.DistanceCumul = ' || New.DistanceCumul 
                           || ', theCumulDistanceAntecedent = '  
                           || theCumulDistanceAntecedent  ;   
                                                                          
        IF New.SensParcours = 'asc' AND New.DistanceCumul <= theCumulDistanceAntecedent THEN
             Erreur = 'La distance cumulée ''' || New.DistanceCumul 
                      || ''' du support '''|| NEW.SupportNo 
                      || ''' doit être supérieure à la distance cumulée  ''' 
                      || theCumulDistanceAntecedent 
                      || ''' du support précédent ''' || thePredecesseur || '''.' ;
            RAISE EXCEPTION SQLSTATE '45002' USING MESSAGE = Erreur ;  
        END IF ;
        
        IF New.SensParcours = 'desc' AND New.DistanceCumul <= theCumulDistanceAntecedent THEN
             Erreur = 'La distance cumulée ''' || New.DistanceCumul || ''' du support '''
                      || NEW.SupportNo 
                      || ''' doit être supérieure à la distance cumulée  ''' 
                      || theCumulDistanceAntecedent 
                      || ''' du support précédent ''' || thePredecesseur || '''.' ;
            RAISE EXCEPTION SQLSTATE '45002' USING MESSAGE = Erreur ;  
        END IF ;
  
        ----------------------------------------------------------------------------------------
        -- Dans le sens ascendant, s'il existe des numéros de supports > au numéro du support 
        -- qu'on crée, la distance cumulée de celui-ci doit être < à la distance cumulée de ces
        --  supports ;
        -- Dans le sens descendant, s'il existe des numéros de supports < au numéro du support
        -- qu'on crée, la distance cumulée de celui-ci doit être < à la distance cumulée de ces
        -- supports.       
        ----------------------------------------------------------------------------------------
    
        IF New.SensParcours = 'asc' THEN    
            theSuccesseur = (SELECT MIN(SupportNo)
                             FROM   SUPPORT
                             WHERE  LigneId = New.LigneId AND  SupportNo > New.SupportNo) ;
        ELSEIF New.SensParcours = 'desc' THEN    
            theSuccesseur = (SELECT MAX(SupportNo)
                             FROM   SUPPORT
                             WHERE  LigneId = New.LigneId
                               AND  SupportNo < New.SupportNo) ;
        END IF ;
        
        IF COALESCE(theSuccesseur, -1) > -1 THEN                          
            theCumulDistanceSuccesseur = (SELECT DistanceCumul
                                          FROM   SUPPORT
                                          WHERE  SupportNo = theSuccesseur) ;
                                              
            IF New.DistanceCumul > theCumulDistanceSuccesseur THEN                   
               RAISE EXCEPTION  USING MESSAGE = 'La distance cumulée ''' || New.DistanceCumul 
                                             || ''' du support '''|| NEW.SupportNo 
                                                || ''' doit être inférieure à la distance cumulée  '''
                                                || theCumulDistanceSuccesseur 
                                             || ''' du support de numéro ''' || theSuccesseur 
                                             || '''.' ;          
            END IF ;      
  
        END IF ;
        
 -------------------------------------------------------------
 -- Pas d'erreur : on ajoute le support et on dégage
 -------------------------------------------------------------
        
        RETURN NEW ; 
           
    END ;
$le_trigger$ 
  LANGUAGE plpgsql ;

CREATE TRIGGER SUPPORT_DISTANCE_COHERENCE_TR BEFORE INSERT ON SUPPORT
    FOR EACH ROW EXECUTE PROCEDURE SUPPORT_DISTANCE_COHERENCE_INSERT_FN() ;

Un début de jeu d’essai 

INSERT INTO COMMUNE (CommuneInsee, CommuneNom) VALUES (56061, 'La Gacilly') ;
INSERT INTO COMMUNE (CommuneInsee, CommuneNom) VALUES (35328, 'Sixt-sur-Aff') ;
INSERT INTO COMMUNE (CommuneInsee, CommuneNom) VALUES (35168, 'Maure-de-Bretagne') ;

-- SELECT *, '' AS 'COMMUNE' FROM COMMUNE ;

INSERT INTO LIGNE (LigneNom, Tension) VALUES ('Ligne 01', 1000) ;
INSERT INTO LIGNE (LigneNom, Tension) VALUES ('Ligne 02', 2000) ;
INSERT INTO LIGNE (LigneNom, Tension) VALUES ('Ligne 03', 3000) ;
INSERT INTO LIGNE (LigneNom, Tension) VALUES ('Ligne 04', 4000) ;

-- SELECT *, '' AS 'LIGNE' FROM LIGNE ;

INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (10, 0 , 1, 1) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (50, 50 , 2, 1) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (60, 68, 3, 1) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (80, 102, 3, 1) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (190, 150, 3, 1) ;

INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (678, 0, 3, 4) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (670, 99, 3, 4) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (640, 210, 3, 4) ;
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (600, 350, 3, 4) ;

INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (87, 130, 3, 1) ; -- injection correcte (asc)
INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (15, 25, 3, 1) ; -- injection correcte (asc)

INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (620, 230, 3, 4) ; -- injection correcte (desc)

--INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (70, 65, 3, 1) ; -- injection erreur distance (asc)

--INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (610, 360, 3, 4) ; -- injection erreur distance (desc)

--INSERT INTO SUPPORT (SupportNo, DistanceCumul, CommuneId, LigneId) VALUES (140, 300, 3, 1) ; -- injection erreur distance (asc)

SELECT LigneId, SupportNo, DistanceCumul, SensParcours FROM SUPPORT ORDER BY LigneId, SupportNo ;

Au résultat :

LigneId    SupportNo    DistanceCumul    SensParcours
-------    ---------    -------------    ------------
      1           10                0    asc
      1           15               25    asc
      1           50               50    asc
      1           60               68    asc
      1           80              102    asc
      1           87              130    asc
      1          190              150    asc

      4          600              350    desc
      4          620              230    desc
      4          640              210    desc
      4          670               99    desc
      4          678                0    desc

Reste à secouer tout ça, histoire de débusquer les bugs...

[LoicL89]

Bonjour fsmrel,


OULA merci pour ce trigger sur les distances cumulées !! Je vais me pencher dessus dès que j'aurai un peu de temps et je vous tiens au courant.

Une autre petite question (qui je pense sera plus facile à résoudre !) : je souhaite calculer dhzt3 (table VEGETATION) à partir de pccm (table ZONE_VEGETATION) et de dh (table VEGETATION). J'aimerais que, si dh associé à prv3 est ≥ 0 alors on le calcule dans la table et on l'insère dans dhzt3, et si ce calcul est < 0 alors on indique 0 dans dhzt3.

J'ai donc écrit la requête suivante :

UPDATE public.vegetation
    SET 
    IF dh - 3,14116*pccm >= 0

        THEN dhzt3 = dh - 3,14116*pccm

    ELSE dhzt3 = 0

    FROM  public.zone_vegetation 
   WHERE public.zone_vegetation.zone_vegetationID = public.vegetation.zone_vegetationID

Et PostgreSQL me renvoie :

ERREUR: erreur de syntaxe sur ou près de « dh »
État SQL :42601
Caractère : 35

Je ne comprends pas trop où est l'erreur, j'ai essayé bien d'autres versions mais pas de résultats. Le problème vient de la structure conditionnelle car lorsque je la supprimer la requête fonctionne bien (mais m'indique des distances négatives...).

Le terme 3,14116 représente une constante bien connue.


Merci,

Cordialement,

Loic

[fsmrel]

Bonsoir Loïc,


Comme son nom l’indique, SQL (Sorry Query Language, mais lisez : « Structured English Query Language ») est structuré, il a une grammaire rigoureuse. Voyez la structure de l’instruction UPDATE : SET doit être suivi du nom d’un attribut dont on veut remplacer la valeur, or je ne sache pas que IF soit le nom d’un attribut de la table VEGETATION (même si certains conifères sont des ifs..). : manifestement dhzt3 conviendrait mieux :

UPDATE VEGETATION
SET dhzt3 = ....

Par exemple :

UPDATE public.VEGETATION
    SET dhzt3 = 
        (
         CASE
             WHEN (dh - 3,14116* pccm >= 0) THEN 
                   dh - 3,14116* pccm
             ELSE 
                  0
         END
         ) 
    FROM public.ZONE_VEGETATION 
    WHERE public.ZONE_VEGETATION.ZoneVegetationId = public.VEGETATION.ZoneVegetationId ;

Cela dit, j'espère que vous arrivez à décrypter le trigger un peu broussailleux, cette sorte de végétation façon jungle pour contrôler les distances cumulées...

[fsmrel]

Tout est bien qui finit bien