Supposons les deux tables suivantes. La première, "object" contient juste une information "name", tandis que la seconde permet de créer des liens orientés entre un objet "parent" et un objet "enfant". Voir le script de création des tables en annexe.
Et un contenu pour chacune de ces tables.
Pour un objet donné (via sa clé primaire), la fonction suivante renvoie la liste des objets parents.
CREATE OR REPLACE FUNCTION get_parent_list_hierarchy(IN _object_id integer)
RETURNS TABLE(fk_id integer, fk_child_id integer, name character varying) AS
$BODY$
WITH RECURSIVE hierarchy(fk_child_id, fk_parent_id) as (
select fk_child_id, fk_parent_id
from object_r_object_hierarchy h
where fk_child_id = _object_id
union all
select r.fk_child_id, r.fk_parent_id
from hierarchy p
inner join object_r_object_hierarchy r
ON r.fk_child_id = p.fk_parent_id
) select h.fk_parent_id, h.fk_child_id, object.name from hierarchy as h
inner join object on h.fk_parent_id = object.pk_object_id
limit 100
;
$BODY$
LANGUAGE sql STABLE
COST 100
ROWS 1000;
Le code de la fonction get_parent_list_hierarchy est en 2 grandes parties :
- la requête récursive (jusqu'à la parenthèse fermante)
- la sélection (après cette parenthèse)
La seconde partie récupère la liste d'id (objet et parent) et y joint les noms des objets.
La première partie est plus complexe et peut également se découper en 2 parties, avant et après le "union all" :
- avant l'union, on construit la liste correspondant à l'id passé en paramètre, on récupère ainsi tous les parents de l'objet concerné
- la partie après l'union fait les appels récursifs :
- "from la liste construite initialement" (nommons-là p), on récupère tous les enregistrements de la table object_r_object_hierarchy (nommons-la r) dont le child_id est égal à un parent_id de p
- les enregistrements obtenus sont ajoutés à p par le "union all"
Le limit 100 à la fin est là pour éviter qu'une requête ne boucle indéfiniment dans le cas où on aurait mal renseigné la table object_r_object_hierarchy en créant un cycle dans la hiérarchie.