hierarchical query

Sql code posted by copied_from_postgres
created at 26 Oct 17:40

Edit | Back 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
WITH RECURSIVE search_graph(parent_id, child_id, depth, path, cycle) AS (
        select g.parent_id, g.child_id, 1,
          ARRAY[child_id, parent_id], // # <1>
          false
        from staffing.skill_mindmap g where g.child_id = 10006
      UNION ALL
        select g.parent_id, g.child_id, sg.depth + 1,
          path  || g.parent_id,
          g.parent_id = ANY(path)
        from staffing.skill_mindmap g, search_graph sg
        where sg.parent_id = g.child_id
        AND NOT cycle [ // <2>

)
SELECT sg.parent_id, skp.desc_short, sg.child_id, skc.desc_short, sg.depth, sg.path, sg.cycle
FROM search_graph AS sg
INNER JOIN staffing.skill AS skp
ON sg.parent_id = skp.id
INNER JOIN staffing.skill AS skc
ON sg.child_id = skc.id
759 Bytes in 2 ms with coderay