Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 7 sur 7
  1. #1
    Membre du Club
    Inscrit en
    mars 2007
    Messages
    254
    Détails du profil
    Informations forums :
    Inscription : mars 2007
    Messages : 254
    Points : 52
    Points
    52

    Par défaut WITH RECURSIVE PATH QUERY

    Bonjour
    J'ai un souci avec la génération du chemin de l'arbre (cf. path dans la doc postgres).
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
     
    CREATE TABLE domaine (
      id CHAR(25) NOT NULL,
      nom CHAR(35) NULL,
      parent CHAR(25) NULL,  
      PRIMARY KEY(id),  
      FOREIGN KEY(parent)REFERENCES Domaine(id)
          ON DELETE CASCADE
          ON UPDATE CASCADE
    );
    insert into domaine values ('1', 'pA', null);
    insert into domaine values ('2', 'pB', '2');
    insert into domaine values ('11', 'eA1', '1');
    insert into domaine values ('12', 'eA2', '1');
    insert into domaine values ('13', 'eA3', '1');
    insert into domaine values ('21', 'eB1', '2');
    insert into domaine values ('22', 'eB2', '2');
    insert into domaine values ('23', 'eB3', '2');
    insert into domaine values ('11-1', 'eeA1', '11');
    insert into domaine values ('11-2', 'eeA1', '11');
    Ensuite la requête basique fonctionne :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    WITH recursive enfant(idEnfant,nomEnfant,level) AS
    (
         SELECT d.id,d.nom,1 
         FROM domaine d WHERE d.id='1'  
       union all
         SELECT d.id,d.nom,(e.level+1)
    	 FROM domaine d,enfant e 
    	 WHERE d.parent=e.idEnfant 
    	 )
    SELECT * FROM enfant ORDER BY level;
    le résultat est dans la PJ resRecOK.png.

    par contre je n'arrive pas à faire fonctionner la requête inspirée de la doc PostgreSql, et intégrant la génération du chemin :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH recursive enfant(idEnfant,nomEnfant,level, path, cycle) AS
    (
         SELECT d.id,d.nom,1 , ARRAY[d.id ], false
         FROM domaine d WHERE d.id='1'  
       union all
         SELECT d.id,d.nom,(e.level+1), path ||d.id , d.id=ANY(path)
    	 FROM domaine d, enfant e 
    	 WHERE d.parent=e.idEnfant 
    	 )
    SELECT * FROM enfant ORDER BY level;
    L'erreur est :
    ERROR: recursive query "enfant" column 4 has type character(25)[] in non-recursive term but type bpchar[] overall
    LINE 3: SELECT d.id,d.nom,1 , ARRAY[d.id ], false
    ^
    HINT: Cast the output of the non-recursive term to the correct type.
    cf. la PJ : resRecKO.png

    Je dois mal utiliser le type ARRAY, pouvez-vous m'éclairer ?
    Images attachées Images attachées

  2. #2
    Expert Confirmé Sénior
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 103
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 32
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 103
    Points : 5 131
    Points
    5 131

    Par défaut

    Bonjour,


    Dans l'idée :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    WITH recursive enfant(idEnfant,nomEnfant,level, path, cycle) AS
    (
         SELECT d.id,d.nom,1 , ARRAY[d.id], false
         FROM domaine d WHERE d.id='1'  
       union ALL
         SELECT d.id,d.nom,(e.level+1), cast(path ||d.id as char(25)[]), d.id=ANY(path)
    	 FROM domaine d, enfant e 
    	 WHERE d.parent=e.idEnfant 
    	 )
    SELECT * FROM enfant ORDER BY level;

  3. #3
    Membre du Club
    Inscrit en
    mars 2007
    Messages
    254
    Détails du profil
    Informations forums :
    Inscription : mars 2007
    Messages : 254
    Points : 52
    Points
    52

    Par défaut rés idée punkoff

    Bonjour

    J'ai bien l'impression que la solution passe par un bon cast.
    Avec votre idée, la requête s'exécute.
    Le résultat ne semble pas bon pour le champ path. Mais c'est peut être juste l'affichage de ce champ ARRAY qui n'est pas bon ?
    Je joins la sortie en PJ resRec_punkoff.png
    Qu'en pensez-vous ?
    Images attachées Images attachées

  4. #4
    Expert Confirmé
    Profil pro
    Inscrit en
    mars 2005
    Messages
    1 682
    Détails du profil
    Informations personnelles :
    Âge : 32
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations forums :
    Inscription : mars 2005
    Messages : 1 682
    Points : 2 531
    Points
    2 531

    Par défaut

    Oui c'est à cause du CHAR qui provoque un padding. Si vous castez votre ID en varchar aux deux endroits, vous n'aurez plus cet effet.

    Par contre on peut fortement s'interroger sur :
    - l'utilisation du type CHAR sur un identifiant
    - la possibilité que vous donnez d'avoir des cycles dans votre arbre et donc l'utilité de les détecter dans votre requête de sélection. D'autant que la récursivité et les cycles ne font pas bon ménage.

  5. #5
    Membre du Club
    Inscrit en
    mars 2007
    Messages
    254
    Détails du profil
    Informations forums :
    Inscription : mars 2007
    Messages : 254
    Points : 52
    Points
    52

    Par défaut

    Bonjour
    J'utilise des char pour id et des cycles juste à titre d'essai pour tester la récursivité.

    Toutes mes tentative de "cast" échouent. Comment le faire ?

    Par ailleurs, comment charger la base demo de postgres (j'aimerais tester leurs exemples avec la table graph) ?

    Merci d'avance

  6. #6
    Expert Confirmé
    Profil pro
    Inscrit en
    mars 2005
    Messages
    1 682
    Détails du profil
    Informations personnelles :
    Âge : 32
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations forums :
    Inscription : mars 2005
    Messages : 1 682
    Points : 2 531
    Points
    2 531

    Par défaut

    Pour le cast de l'ID en varchar :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH recursive enfant(idEnfant,nomEnfant,level, path, cycle) AS
    (
         SELECT d.id,d.nom,1 , ARRAY[CAST(d.id as VARCHAR)], false
         FROM domaine d WHERE d.id='1'  
       union ALL
         SELECT d.id,d.nom,(e.level+1), path || CAST(d.id AS VARCHAR), d.id=ANY(path)
    	 FROM domaine d, enfant e 
    	 WHERE d.parent=e.idEnfant 
    	 )
    SELECT * FROM enfant ORDER BY level;
    Quand bien même c'est à des fin de tests, stocker des entiers en char, qui plus est, pour des colonnes clés primaire, c'est à proscrire.

  7. #7
    Membre du Club
    Inscrit en
    mars 2007
    Messages
    254
    Détails du profil
    Informations forums :
    Inscription : mars 2007
    Messages : 254
    Points : 52
    Points
    52

    Par défaut

    Bonjour

    merci, avec le cast proposé tout fonctionne à merveille.

    J'insiste quand même pour demander :
    Comment trouver un dump de la base de demo postgres ?

    Sinon, vmolines :
    pour le test de "cycle", nous avons des arbres dont le départ "s'auto référence" (id=parent).
    Dans ma table domaine, c'est le cas d'arbre pour id=2.
    Sans la solution "and not d.id=ANY(path)" ci-dessous, la requête part en boucle sans fin.
    Curieusement "and not cycle" ne suffit pas : la requête ne génère pas de boucle, mais le premier enregistrement apparaît autant que level 1 et level 2 du fait d'auto reférencement.
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    WITH recursive enfant(idEnfant,nomEnfant,level, path, cycle) AS
    (
         SELECT d.id,d.nom,1 , ARRAY[CAST(d.id AS VARCHAR)], false
         FROM domaine d WHERE d.id='2'  
       union ALL
         SELECT d.id,d.nom,(e.level+1), path || CAST(d.id AS VARCHAR), d.id=ANY(path)
    	 FROM domaine d, enfant e 
    	 WHERE d.parent=e.idEnfant and not d.id=ANY(path)
    	 )
    SELECT * FROM enfant ORDER BY level;
    J'ai voulu donc tester cette solution. Et cela fonctionne.
    Grand merci à tous les participants.
    En espérant d'aider certains qui chercheront un jour à comprendre cette syntaxe SQL particulière.

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •