Forums PostgreSQL.fr

Géronimo

Membre

Nommer les récursivités d'une requête récursive

Bonjour !

Je me permets de poster à nouveau ce sujet, mais dans un autre topic qui me semble plus adapté, car la fonction que j'avais écrite sur l'autre sujet (http://forums.postgresql.fr/viewtopic.php?id=3155) ne permet pas de nommer la récursivité d'une requête récursive comme il faut. Elle se contente de prendre la valeur maximum des code_zone et la met comme nom.

Voilà la table sur laquelle je travaille. Elle s'appelle init recurs_ord_2 :

gid ; rnk_desc ; profondeur ; code_hydro ; c_hyd_cdo, code_zone, lettre_bv, toponyme1, fpkh, tpkh, id_nd_ini, id_nd_fin

1;12;1;"V7300000";"V---0000";"V730";"V";"fleuve le rhône";947663;1000000;619003051;619005470
2;11;1;"V7210000";"V---0000";"V721";"V";"fleuve le rhône";944880;947663;619002712;619003051
3;10;1;"V7200000";"V---0000";"V720";"V";"fleuve le rhône";931497;944880;619001184;619002712
4;9;1;"V7000000";"V---0000";"V700";"V";"fleuve le rhône";916913;931497;614010566;619001184
5;8;1;"X3500000";"X---0000";"X350";"X";"rivière la durance";975886;1000000;619000786;614010566
6;7;1;"X3480400";"X34-0400";"X348";"X";"rivière le coulon";975108;1000000;619000784;619000786
7;6;1;"X3460400";"X34-0400";"X346";"X";"rivière le coulon";963787;975108;619000560;619000784
8;5;1;"X3450400";"X34-0400";"X345";"X";"rivière le calavon";955523;963787;615009286;619000560
9;4;1;"X3430400";"X34-0400";"X343";"X";"rivière le calavon";938396;955523;615009662;615009286
10;3;1;"X3420400";"X34-0400";"X342";"X";"rivière le calavon";935022;938396;615009013;615009662
11;2;1;"X3410400";"X34-0400";"X341";"X";"rivière le calavon";922089;935022;614009387;615009013
12;1;1;"X3400400";"X34-0400";"X340";"X";"rivière le calavon";913111;922089;614008612;614009387

Ce que je veux faire, c'est une récursivité par l'égalité id_nd_fin=id_nd_ini.
Cette table représente les tronçons hydrographiques de cours d'eau qui se suivent. Pour chacun des tronçons, de part et d'autres, il y a un identifiant : id_nd_ini pour l'extrémité la plus en amont et id_nd_fin pour l'extrémité la plus en aval. On comprend donc bien que, l'id_nd_fin d'un tronçon est égal à l'id_nd_ini du tronçon situé en aval.
Je veux donc que, pour chaque tronçon, le logiciel m'indique la liste des tronçons qui lui sont en amont.
Cela j'y arrive.

Ce que je veux, en plus, c'est que sur une des colonnes de la table qui m'est renvoyée, pour chaque liste, il soit indiqué le la nomenclature du tronçon dont est déployé toute la listes des tronçons qui lui sont en amont (l'identifiant code_zone).
Et ça je n'arrive pas à le faire...

J'arrive à ce qu'il me donne la récursivité pour chaque tronçon sans rangement par code_zone avec le code suivant :

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
union ALL
SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini )
SELECT * FROM rec

La table qui m'est retournée est :

gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link
12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V721";"V721";"V";619002712;619003051;2
10;1;"V720";"V720";"V";619001184;619002712;3
9;1;"V700";"V700";"V";614010566;619001184;4
8;1;"X350";"X350";"X";619000786;614010566;5
7;1;"X348";"X348";"X";619000784;619000786;6
6;1;"X346";"X346";"X";619000560;619000784;7
5;1;"X345";"X345";"X";615009286;619000560;8
4;1;"X343";"X343";"X";615009662;615009286;9
3;1;"X342";"X342";"X";615009013;615009662;10
2;1;"X341";"X341";"X";614009387;615009013;11
1;1;"X340";"X340";"X";614008612;614009387;12
11;1;"V730";"V721";"V";619002712;619003051;2
10;1;"V721";"V720";"V";619001184;619002712;3
9;1;"V720";"V700";"V";614010566;619001184;4
8;1;"V700";"X350";"X";619000786;614010566;5
7;1;"X350";"X348";"X";619000784;619000786;6
6;1;"X348";"X346";"X";619000560;619000784;7
5;1;"X346";"X345";"X";615009286;619000560;8
4;1;"X345";"X343";"X";615009662;615009286;9
3;1;"X343";"X342";"X";615009013;615009662;10
2;1;"X342";"X341";"X";614009387;615009013;11
1;1;"X341";"X340";"X";614008612;614009387;12
etc...
3;1;"V730";"X342";"X";615009013;615009662;10
2;1;"V721";"X341";"X";614009387;615009013;11
1;1;"V720";"X340";"X";614008612;614009387;12
2;1;"V730";"X341";"X";614009387;615009013;11
1;1;"V721";"X340";"X";614008612;614009387;12
1;1;"V730";"X340";"X";614008612;614009387;12

78 lignes en tout.

J'arrive à avoir un rangement en fonction de code_zone, mais seulement sur une récursivité, avec cette fonction :

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, profondeur, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
WHERE rnk_desc=1
union ALL
SELECT b.gid, a.profondeur, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini AND a.rnk_link + 1 = b.rnk_desc)
SELECT * FROM rec

Qui me donne cette table :

gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link

12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V730";"V721";"V";619002712;619003051;2
10;1;"V730";"V720";"V";619001184;619002712;3
9;1;"V730";"V700";"V";614010566;619001184;4
8;1;"V730";"X350";"X";619000786;614010566;5
7;1;"V730";"X348";"X";619000784;619000786;6
6;1;"V730";"X346";"X";619000560;619000784;7
5;1;"V730";"X345";"X";615009286;619000560;8
4;1;"V730";"X343";"X";615009662;615009286;9
3;1;"V730";"X342";"X";615009013;615009662;10
2;1;"V730";"X341";"X";614009387;615009013;11
1;1;"V730";"X340";"X";614008612;614009387;12

12 lignes

J'aimerais savoir si vous pouvez m'indiquer quel code utiliser pour avoir l'ensemble des récursivités (78 lignes comme sur la 1ère table) avec le rangement par code_zone (comme sur la deuxième table).

Je suis désolé pour le long poste, mais ça me semble indispensable pour tout décrire.

Je vous remercie d'avance pour vos futures réponses,

Bonne soirée.

Géronimo

Membre

Re : Nommer les récursivités d'une requête récursive

Désolé,
Je n'avais pas écrit correctement la 1ère fonction et la table qui en découlait hier soir. Surement la fatigue...

Donc, voilà la fonction :

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link) AS (
SELECT gid, 1, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc
FROM init_recurs_ord_2
union ALL
SELECT b.gid, a.profondeur +1, code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc
FROM rec a
INNER JOIN init_recurs_ord_2 b ON b.id_nd_fin = a.id_nd_ini)
SELECT * FROM rec

Et voilà la table qui en découle :

12;1;"V730";"V730";"V";619003051;619005470;1
11;1;"V721";"V721";"V";619002712;619003051;2
10;1;"V720";"V720";"V";619001184;619002712;3
9;1;"V700";"V700";"V";614010566;619001184;4
8;1;"X350";"X350";"X";619000786;614010566;5
7;1;"X348";"X348";"X";619000784;619000786;6
6;1;"X346";"X346";"X";619000560;619000784;7
5;1;"X345";"X345";"X";615009286;619000560;8
4;1;"X343";"X343";"X";615009662;615009286;9
3;1;"X342";"X342";"X";615009013;615009662;10
2;1;"X341";"X341";"X";614009387;615009013;11
1;1;"X340";"X340";"X";614008612;614009387;12
11;2;"V730";"V721";"V";619002712;619003051;2
10;2;"V721";"V720";"V";619001184;619002712;3
9;2;"V720";"V700";"V";614010566;619001184;4
8;2;"V700";"X350";"X";619000786;614010566;5
7;2;"X350";"X348";"X";619000784;619000786;6
6;2;"X348";"X346";"X";619000560;619000784;7
5;2;"X346";"X345";"X";615009286;619000560;8
4;2;"X345";"X343";"X";615009662;615009286;9
3;2;"X343";"X342";"X";615009013;615009662;10
2;2;"X342";"X341";"X";614009387;615009013;11
1;2;"X341";"X340";"X";614008612;614009387;12
10;3;"V730";"V720";"V";619001184;619002712;3
9;3;"V721";"V700";"V";614010566;619001184;4
8;3;"V720";"X350";"X";619000786;614010566;5
7;3;"V700";"X348";"X";619000784;619000786;6

etc...

3;10;"V730";"X342";"X";615009013;615009662;10
2;10;"V721";"X341";"X";614009387;615009013;11
1;10;"V720";"X340";"X";614008612;614009387;12
2;11;"V730";"X341";"X";614009387;615009013;11
1;11;"V721";"X340";"X";614008612;614009387;12
1;12;"V730";"X340";"X";614008612;614009387;12

On voit bien que ma deuxième colonne, la colonne profondeur a un numéro différent pour chaque récursivité. Je voudrais que, sur la troisième colonne (code_zone_max), apparaisse le nom du tronçon (code_zone) sur lequel s'effectue chaque récursivité.
Ainsi, à côté du 1 de la colonne profondeur pour la 1ère récursivité, il s'afficherait V730 sur les 12 lignes.
A côté du 2 de la colonne profondeur pour la 2e récursivité, il s'afficherait V720 sur 11 lignes.
...
A côté du 10 de la colonne profondeur pour la 10e récursivité, il s'afficherait X342 sur 3 lignes.
A côté du 11 de la colonne profondeur pour la 11e récursivité, il s'afficherait X341 sur 2 lignes.
A côté du 12 de la colonne profondeur pour la 12e récursivité, il s'afficherait X340 sur 1 lignes.

Je ne vois pas comment faire...
Merci de vos futures réponses.

Géronimo

Membre

Re : Nommer les récursivités d'une requête récursive

Bijour !!!

Voilà le code qui m'a permis de résoudre mon problème :

WITH recursive rec (gid, profondeur, code_zone_max, code_zone, id_nd_ini, id_nd_fin, geom, chemin) AS (
SELECT gid, 1, code_zone, code_zone, id_nd_ini, id_nd_fin, geom, code_zone::text
FROM init_recurs_ord_7
union ALL
SELECT b.gid, a.profondeur +1, a.code_zone_max, b.code_zone, b.id_nd_ini, b.id_nd_fin, b.geom, a.chemin || ' > ' || b.code_zone
FROM rec a
INNER JOIN init_recurs_ord_7 b ON a.id_nd_fin = b.id_nd_ini)
SELECT * FROM rec
ORDER BY 1, profondeur DESC