Dump all of the features related to a starting feature (hardcoded....) using a recursive common table expression.

feature-dump-1.sql

-- This works, starts with a feature and lists all of the features
-- it's related to, deals with cycles, homology seems to be reflexive
-- and symmetric.
with recursive
  -- info about a subject/object pair
  pair(initial_feature,
       subj_feature_id, subj_name, subj_uniquename, subjterm_name,
       relterm_name,
       obj_feature_id, obj_name, obj_uniquename, objterm_name,
       feat_id_path,
       cycle
      ) as
  (
   select subj.feature_id,
          subj.feature_id, subj.name, subj.uniquename, subjterm.name,
          relterm.name as relationship,
          obj.feature_id, obj.name, obj.uniquename, objterm.name,
          array[subj.feature_id, obj.feature_id],
          false
     from feature subj
          inner join feature_relationship fr on
                     subj.feature_id = fr.subject_id
          inner join feature obj on
                     obj.feature_id = fr.object_id
          inner join cvterm subjterm on
                     subjterm.cvterm_id = subj.type_id
          inner join cvterm objterm on
                     objterm.cvterm_id = obj.type_id
          inner join cvterm relterm on
                     relterm.cvterm_id = fr.type_id
     where
           subj.feature_id = 54794423
   union all
   select null,
          p.obj_feature_id, p.obj_name, p.obj_uniquename, p.objterm_name,
          relterm.name as relationship,
          obj.feature_id, obj.name, obj.uniquename, objterm.name,
          p.feat_id_path || obj.feature_id,
          obj.feature_id = any(p.feat_id_path)
     from pair p
          inner join feature_relationship fr on
                     p.obj_feature_id = fr.subject_id
          inner join feature obj on
                     fr.object_id = obj.feature_id
          inner join cvterm relterm on
                     relterm.cvterm_id = fr.type_id
          inner join cvterm objterm on
                     objterm.cvterm_id = obj.type_id
     where not p.cycle
  )
  select initial_feature,
         subj_feature_id, subj_name, subj_uniquename, subjterm_name,
         relterm_name,
         obj_feature_id, obj_name, obj_uniquename, objterm_name,
         feat_id_path,
         cycle
  from pair;