Récursivité SQL PL/SQL (Postgresql)

[sekaijin]

Salut

J'ai une table hiérarchique

Code :

1
2
3
4
5
6
CREATE TABLE workgroup (
wg_id INTEGER NOT NULL;
wg_parent_id INTEGER NULL;
wg_label VARCHAR(255);
...
);

j'ai donc dans cette table des groupes hiérarchisés
par exemple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
1, NULL, SUPER ADMIN
2, 1, ADMIN A
3, 1, ADMIN B
4, 2, USER a
5, 2, USER b
6, 2, USER b
7, 2, USER d
8, 2, USER e
9, 3, USER 1
10, 3, USER 2
11, 3, USER 3
12, 3, USER 4

Je cherche à trouver tous les descendants d'un group
par exemple sur le SUPER ADMIN je récupère tout
mais sur l'ADMIN A je veux

Code :

1
2
3
4
5
6
2, 1, ADMIN A
4, 2, USER a
5, 2, USER b
6, 2, USER b
7, 2, USER d
8, 2, USER e

Mon problème est que je ne connais pas à l'avance la profondeur de l'arbre
la seule solution que j'ai trouvé créer une fonction récursive
mais ça plante!!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION testQuery(INT) RETURNS SETOF workgroup AS 
'
DECLARE 
   _row           workgroup%ROWTYPE;
   _wg_id        ALIAS FOR $1;
 
BEGIN
 
    SELECT INTO _row * FROM workgroup WHERE wg_id = _wg_id OR wg_parent_id = _wg_id;
    WHILE  FOUND LOOP
        RETURN NEXT _row;
        SELECT INTO _row * FROM workgroup WHERE wg_parent_id = _row.wg_id;
    END LOOP;
    RETURN;
END
 
' LANGUAGE 'plpgsql';
SELECT * FROM testQuery(1);

le problème avec cette façon de faire c'est que je ne parcours qu'une seule branche
j'obtiens donc

Code :

1
2
3
1, NULL, SUPER ADMIN
2, 1, ADMIN A
4, 2, USER a

me manque donc tous les autres fils d'ADMIN A et ADMIN B et ses fils
j'ai donc pensé rappeler ma fonction sur chaque fils
genre

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION testQuery2(INT) RETURNS SETOF workgroup AS 
'
DECLARE 
   _row           workgroup%ROWTYPE;
   _wg_id        ALIAS FOR $1;
 
BEGIN
 
    SELECT INTO _row * FROM workgroup WHERE wg_parent_id = _wg_id ;
    WHILE  FOUND LOOP
        RETURN NEXT _row;
        SELECT INTO _row * FROM testQuery2(_row.wg_id);
    END LOOP;
    RETURN;
END
 
' LANGUAGE 'plpgsql';
SELECT * FROM testQuery2(1);

Je perd la racine mais ça encore ce n'est pas grave mais contrairement à ce que je pensais je ne parcours pas tous les fils.

A+JYT

[JeitEmgie]

Envoyé par sekaijin

Salut

J'ai une table hiérarchique

…
Je perd la racine mais ça encore ce n'est pas grave mais contrairement à ce que je pensais je ne parcours pas tous les fils.

A+JYT

la méthode classique pour dérécursifier une méthode est d'utiliser un "stack"…

dans le contexte de PL/SQL, ce stack pourrait être une table temporaire
et le résultat devant être collecter sous forme d'un ensemble on pourrait aussi l'implémenter sous forme de table temporaire…


en "pseudo" code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
CREATE temp TABLE wg_stack (id int);
CREATE temp TABLE wg_result (id int);
 
-- la racine fait partie des résultats
INSERT INTO wg_result(id) VALUES(_wg_id);
 
-- mettre dans le stack les premiers fils
INSERT INTO wg_stack SELECT id FROM workgroup WHERE wg_parent_id = _wg_id ;
 
-- "pop" le prochain id à traiter 
SELECT INTO current_id id FROM wg_stack LIMIT 1 ;
while found loop
    DELETE FROM wg_stack WHERE id = current_id ;
 
    INSERT INTO wg_stack SELECT id FROM workgroup WHERE wg_parent_id = current_id ;
    INSERT INTO wg_result SELECT id FROM workgroup WHERE wg_parent_id = current_id ;
 
    SELECT INTO current_id id FROM wg_stack LIMIT 1 ;
end loop ;
 
 
-- wg_result contient tous les fils de _wg_id
FOR r IN SELECT DISTINCT id FROM wg_result
    RETURN next _row ;
end loop ;
 
RETURN ;