I have this table "con.cuenta"
I use connectby
to obtain the levels:
SELECT t.cue_id, t.cue_id_ref, (t.level+1) nivel, t.branch
FROM connectby('con.cuenta', 'cue_id', 'cue_id_ref', '1', 0,'/')
AS t(cue_id bigint, cue_id_ref bigint, level int,branch text)
The problem is that I have several roots and the connectby method works with a single root. Some alternative?
The connectby
extension is pretty much obsolete since the introduction of recursive CTEs in Postgres 8.4
With a recursive CTE, it's simply to deal with multiple trees by using the correct WHERE clause for the non-recursive part:
with recursive tree as (
select cue_id,
cue_id_ref,
1 as level,
cue_id::text as branch
from "con.cuenta"
where cue_id_ref is null
union all
select c.cue_id,
c.cue_id_ref,
p.level + 1,
concat(p.branch, '/', c.cue_id)
from "con.cuenta" c
join tree p on p.cue_id = c.cue_id_ref
)
select cue_id, cue_id_ref, level as nivel, branch
from tree;