WITH RECURSIVE temp1(hierarchy,id,parent_id,industry_tree_name,order_by,lvl) AS
(
select ARRAY[it.industry_tree_id] AS hierarchy,
it.industry_tree_id as id,
it.industry_tree_parent_id as parent_id,
it.industry_tree_name,
it.order_by,
it.lvl
from industry_tree it
where it.industry_version_id = 11
and it.active_from <= current_timestamp
and it.active_to > current_timestamp
union
select temp1.hierarchy || t2.industry_tree_id AS hierarchy,
t2.industry_tree_id as id,
t2.industry_tree_parent_id as parent_id,
t2.industry_tree_name,
t2.order_by,
temp1.lvl+1 as lvl
from industry_tree t2
INNER JOIN temp1 on (temp1.id = T2.industry_tree_parent_id)
and t2.active_from <= current_timestamp
and t2.active_to > current_timestamp
)
select *
from temp1
ORDER BY hierarchy;
Взято с stackoverflow.com
