Discussion :

[Jean-Marc68]

Salut à toutes et à tous,

Soit une table contenant, entre autre, un champs no_lot et un champs remplace.
Si un numéro de lot est présent dans le champs remplace, c'est que le lot écrit dedans est remplacé par le lot de la ligne.
Le lot remplacé, peut lui aussi avoir remplacé un autre lot.
Exemple
no_lot | remplace
-----------------------
1 |
2 |
3 |
4 | 2
5 | 3
6 | 4
7 | 6

Je cherche à créer une fonction qui donnerait toute la liste des lots qu'il remplace.
Par exemple, si je met SELECT * FROM fonction(7), il devrait me sortir les 3 lignes 6, 4, 2.

Le problème c'est que je ne vois pas du tout comment gérer la récursivité nécessaire (je suppose).

Merci de vos z'avis z'avisé,
JM

[alassanediakite]

Salut
Une solution...
La fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace function frecursive(i int) returns setof record 
as
$$
declare 
tligne t%rowtype;
r int;
begin
select no_lot, remplace into tligne from t where no_lot=i;
r=tligne.remplace;
while exists (select no_lot from t where no_lot=r)
loop
select no_lot, remplace into tligne from t where no_lot=r;
r=tligne.remplace;
return next tligne;
end loop;
end;
$$
language plpgsql

L'appel de la fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select no_lot from frecursive(7) as (no_lot int, remplace int)

@+

[punkoff]

Bonjour,


La requête qui permet de répondre au besoin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
with recursive tmp (id, id_fils, niveau) as (
select id, id_fils, 1 
from pere_fils
where id = 7
union all
select b.id, b.id_fils, a.niveau + 1
from tmp a
inner join pere_fils b on a.id_fils = b.id)
select *
from tmp
where niveau > 1

Plus qu'a intégrer ca dans une procédure et gérer le binding de la variable.

[Jean-Marc68]

Merci beaucoup pour votre aide,

Suite à vos réponses et à mes connaissances, bien inférieures aux vôtres, j'ai écris une fonction qui fonctionne sur base de la réponse de alassanediakite. J'ai eu un pbl avec le mot recursive de la fonction de punkoff que mon PostgreSQL (v. 8.3) n'a pas l'air de digérer.

A partir de cela j'ai essayé de faire la procédure inverse, mais je n'y arrive pas.

Connaissez-vous une fonction pour faire l'inverse, soit partir du sommet de l'arbre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
1       2       3
       / \     /
      4   7   5
     / \   \ /
    6   8   9

Grâce à la fonction que j'ai créée avec votre aide, je suis capable, à partir de 6, d'obtenir 4, 2.
J'aurais besoin de pouvoir descendre l'arbre aussi. Soit en partant de 4, obtenir 4, 6, 8, ou en partant de 2, obtenir 4, 7, 6, 8, 9.
J'ai cherché et trouvés des choses que je comprend en partie, mais pas en entier, et visiblement pas assez pour créer ma fonction.
J'y ai vu, entre autre, une question de niveau, ce qui semble intéressant, mais comment faire ?

Je viens aussi d'apprendre qu'il se peut qu'un élément en remplace plusieurs (cfr 9 dans le graphe ci-dessus). Dans ce cas ma fonction de "hiérarchie montante" ne fonctionne plus correctement puisque si je pars de 9, je ne remonte que la première branche trouvée.

Les tables que j'ai, et les colonnes impliquées sont :
Table lots :
nouv_lot character varying(10) NOT NULL

Table remplacements_lots :
nouv_lot_remplace character varying(10) NOT NULL
nouv_lot_remplacant character varying(10) NOT NULL

La fonction que j'avais écrite à partir du code de alassanediakite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION topocom_lotsremplace(no_lot character varying)  RETURNS SETOF lots AS
$BODY$
declare 
ret lots%rowtype;
r varchar(10);
begin
    select nouv_lot_remplace into r from remplacements_lots where nouv_lot_remplacant=no_lot;
    select * from lots where nouv_lot = r into ret;
    return next ret;
    while (r IS NOT NULL)
    loop
        select nouv_lot_remplace into r from remplacements_lots where nouv_lot_remplacant=r;
        select * from lots where nouv_lot = r into ret;
        return next ret;
    end loop;
end;
$BODY$
  LANGUAGE plpgsql;

Pouvez-vous m'aider ?
Merci,
JM

[SQLpro]

Avec une requête récursive, c'est juste un parcours de graphe. Pour éviter de boucler il faut conserver dans les étapes d'itération récursive la liste des points de passage (chemin) dans un tableau et vérifier à chaque itération que la destination n'est pas déjà dans le chemin.

Lisez ce que j'ai écrit au sujet des CTE récursives : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[Jean-Marc68]

Merci beaucoup SQLpro. Je m'en vais étudier ce que tu as écris.

JM