1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
DROP VIEW init_recurs_join CASCADE
CREATE or replace VIEW init_recurs_join as
WITH recursive tmp (gid, profondeur, code_zone, lettre_bv, id_nd_ini, id_nd_fin, chemin, boucle) AS (
SELECT gid, 1, code_zone, lettre_bv, id_nd_ini, id_nd_fin, ARRAY [gid], false
FROM init_recurs_ord
union ALL
SELECT b.gid, a.profondeur +1, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, chemin || b.gid, b.gid=ANY(chemin)
FROM init_recurs_ord b
INNER JOIN tmp a ON b.id_nd_fin = a.id_nd_ini )
SELECT gid, profondeur, code_zone, lettre_bv, id_nd_ini, id_nd_fin,
row_number() over(partition BY profondeur ORDER BY gid desc) AS rnk_desc, chemin, boucle
FROM tmp
WITH recursive rec (gid, profondeur, code_zone_max, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_link, chemin) AS (
SELECT gid, 1, code_zone, code_zone, lettre_bv, id_nd_ini, id_nd_fin, rnk_desc, '' || code_zone AS chemin
FROM init_recurs_join
WHERE rnk_desc = 1
union ALL
SELECT b.gid, a.profondeur+1, a.code_zone_max, b.code_zone, b.lettre_bv, b.id_nd_ini, b.id_nd_fin, b.rnk_desc, a.chemin || ' > ' || b.code_zone
FROM rec a
INNER JOIN init_recurs_join b ON b.id_nd_fin = a.id_nd_ini AND a.rnk_link + 1 = b.rnk_desc)
SELECT * FROM rec |
Partager