Как в SQL собрать все взаимозамены?

Question

[eldar_web]

Никакие идеи пока не приходить в голову.
Как можно по SQL-запросу (используется Postgresql) собрать взаимозамены по группам?
Допустим, есть таблица Foo со структурой:

main | cross
x | a
x | b
b | x
b | h
z | t
....

main взаимозаменяет cross, и наоборот. Надо собрать все взаимозамены в одну строку.
На выводе должно выйти группа:
1) x - a - b - h
2) z - t
Как можно реализовать?

Answer 1

[Дмитрий Телепнев]

with recursive table_recursive as (
	with "table" as (
		select *
		from (
			values('x','a'),
			('a','b'),
			('b','c'),
			('c','d'),
			('n','k'),
			('k','l')
		) as _(main,"cross")
	)
	select *, t.main || ' -> ' || t."cross" as "path"
	from "table" as t
	
	union
	
	select t_r.main, t."cross", t_r."path" || ' -> ' || t."cross" as "path"
	from table_recursive t_r
	join "table" t on t.main = t_r."cross"
)
select t_r.*
from table_recursive t_r
where
	t_r."main" not in (select "cross" from table_recursive)
	and t_r."cross" not in (select "main" from table_recursive)

Нужно учитывать, что в такой интерпретации, если таблица большая - то будет fullscan

Сходу не придумал как с этим бороться