Dump the features related to a hard-coded starting feature, using two common table expressions (one recursive to build the set, one not recursive to expand set of columns)

feature-dump-2.sql

-- Here's an example that takes the output of the recursive CTE
-- and then decorates it with details about the features and relationships.
with recursive pair(initial_feature,
                    subj_feature_id, relationship_id, obj_feature_id,
                    feat_id_path,
                    cycle
                    ) as
  (
   select subj.feature_id,
          subj.feature_id, fr.feature_relationship_id, obj.feature_id,
          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
     where
        subj.feature_id = 54794423
   union
   select null,
          p.obj_feature_id, fr.feature_relationship_id, obj.feature_id,
          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
                     obj.feature_id = fr.object_id
     where not cycle
  ),
 decorated_pair(initial_feature,
                subj_feature_id, subject_name, subject_uniquename, subject_type,
                relationship_name,
                obj_feature_id, object_name, object_uniquename, object_type,
                feat_id_path, cycle
               ) as
  (
        select initial_feature,
               s.feature_id, s.name, s.uniquename, s_cvterm.name as sub_type,
               rel_cvterm.name as relationship_name,
               o.feature_id, o.name, o.uniquename, o_cvterm.name as obj_type,
               feat_id_path,
               cycle
        from pair
             inner join feature s on
                        pair.subj_feature_id = s.feature_id
             inner join feature_relationship fr on
                        pair.relationship_id = fr.feature_relationship_id
             inner join feature o on
                        pair.obj_feature_id = o.feature_id
             inner join cvterm s_cvterm on
                        s.type_id = s_cvterm.cvterm_id
             inner join cvterm rel_cvterm on
                        fr.type_id = rel_cvterm.cvterm_id
             inner join cvterm o_cvterm on
                        o.type_id = o_cvterm.cvterm_id
  )
  select initial_feature,
         subj_feature_id, subject_name, subject_uniquename, subject_type,
         relationship_name,
         obj_feature_id, object_name, object_uniquename, object_type,
         feat_id_path,
         cycle
  from decorated_pair;