1
2
3
4
5
6
7
--recursive + inner join
with recursive temp as (
    select class_id,mother_id,class_id::text as path, class_name from item_class --and class_id=123
    union all
    select iic.class_id,iic.mother_id,iic.class_id::text||'/'||temp.path as path, iic.class_name||'/'||temp.class_name as class_name from item_class iic inner join temp on temp.mother_id=iic.class_id
)
select concat('/',path,'/') as path, class_name,split_part(path,'/',LENGTH(path) - LENGTH(REPLACE(path, '/', ''))+1) as  class_id from temp where mother_id=0 and position('/' in path)>0