WITH RECURSIVE PATH QUERY [Résolu]

[msomso]

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 ?

[punkoff]

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;

[msomso]

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 ?

[vmolines]

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.

[msomso]

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

[vmolines]

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.

[msomso]

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.