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;

La requête est stockée dans une fonction qui renvoie un table contenant l'id et le nom de l'objet ainsi que l'id de son parent. Il n'y a aucune contrainte sur l'unicité des parents : un objet peut avoir plusieurs parents au même titre qu'il peut avoir plusieurs enfants. On est dans ici dans la cas d'un graphe orienté et pas simplement d'une arborescence. Le cas d'une arborescence aurait été géré en injectant le fk_parent_id (nullable) dans la table object et en éliminant alors la table de relation object_r_object_hierarchy.

Le code de la fonction get_parent_list_hierarchy est en 2 grandes parties :
  1. la requête récursive (jusqu'à la parenthèse fermante)
  2. 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" :
  1. 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é
  2. 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.