Follow the connections : recursive query

i have two columns of numbers. the goal is starting from a number f.e. 55, to extract all ‘connected’ numbers (starting with any of those numbers, should yield the same result)

 A  B
-----
56  55
55  56
69  55
35  55
47  55
60  55
22  55
26  47
 2  35
..... more data

in this case all numbers shown here : 55,56,35,69,60,22,47,2,26

I use the following query :

with recursive merge as (
   select A from T where A = 55 or B = 55
   union 
   select B 
    from T cm join merge m 
       on cm.A = m.A or cm.B = m.A
)
select * from merge

but I get back only those back : 55,56,35,69,60,22,47

I think using this will work :

with recursive merge as (
   select A from T where A = 55 or B = 55
   union 

  (
   select B 
    from T cm join merge m 
       on cm.A = m.A
    union
   select A 
    from T cm join merge m 
       on cm.B = m.A
  )
)
select * from merge

but Postgres does not allow to use “merge” more than ONCE in the recursive query !!?

My goal is starting with number to find all connected numbers across the “chain” i.e.

  55 => 56,35,69,60,22,47 => 2,26 ....

because :

  47 => 26
  35 => 2

Answer

You can do:

with recursive
n (v) as (
  select case when t.a = 55 then t.b else t.a end
  from t
  where t.a = 55 or t.b = 55
 union
  select case when t.a = n.v then t.b else t.a end
  from n
  join t on t.a = n.v or t.b = n.v
)
select * from n

Result:

 v
--
56
69
35
47
60
22
55
 2
26

See running example at DB Fiddle.

Or… if you prefer to place the parameter in a single place:

with recursive
params as (select 55 as x), -- parameter only once
n (v) as (
  select case when t.a = p.x then t.b else t.a end
  from t
  cross join params p 
  where t.a = p.x or t.b = p.x
 union
  select case when t.a = n.v then t.b else t.a end
  from n
  join t on t.a = n.v or t.b = n.v
)
select * from n