recursive-parents.sql

with recursive cte as (
  select
    child_id,
    parent_id,
    project_id,
    array [child_id, parent_id] as path,
    1 as level
  from
    child_parent
  union all
  select
    p.child_id as child_id,
    t.parent_id as parent_id,
    t.project_id,
    p.child_id || t.path as path,
    t.level + 1 as level
  from
    child_parent p
      inner join cte t on p.parent_id = t.child_id
  where
    not (p.child_id = any (t.path))
)
select
  cte.level,
  cte.path
from
  cte
order by
  cte.path;