connectby with more than one root and dinamic-CodePudding

I have this table "con.cuenta"

enter image description here

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)

enter image description here

The problem is that I have several roots and the connectby method works with a single root. Some alternative?

CodePudding user response：

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;