POSTGRES :: JIRA :: Issuelink Hierarchy

-- POSTGRES

-- Berechnung aller Pfade zu Kind-Issues modelliert via Issue-Links inklusive Zykle-Erkennung.
-- 10000 : id des Vater Kind Links
-- 11371 : id des Projektes/aktueller Monatsabschuss

WITH RECURSIVE
hissuelink(id,SOURCE,destination) AS (SELECT id,SOURCE,destination FROM issuelink WHERE linktype = 10000),
hierarchy( hlevel, hdest, hpatharray, cycle ) AS (
  SELECT 0, id, ARRAY[(CAST(id AS VARCHAR))], FALSE FROM  jiraissue i  WHERE project = 11371 AND i.id NOT IN (SELECT destination FROM hissuelink)
  UNION ALL (
          SELECT hlevel + 1, destination, hpatharray || (CAST(destination AS VARCHAR)), (CAST(destination AS VARCHAR)) = ANY(hpatharray)
          FROM hissuelink link, hierarchy h
          WHERE link.SOURCE = hdest AND NOT cycle
  )
)

SELECT * FROM hierarchy ORDER BY hlevel;

-- Literatur:
-- * IX 1/2013 SQL-Know-how (Oracle)
-- * http://www.postgresql.org/docs/9.1/static/queries-with.html