postgre with递归查询组织路径

with递归查询组织路径

SELECT r.id,
    (array_to_string(
            array(
                    select name from (
                            with recursive rec as(
                                            select t.id,t.pid,t.name from t_org t where t.id = r.id
                                            union all
                                            select t1.id,t1.pid,t1.name from t_org t1 join rec r on r.pid = t1.id
                                    ) select id,name from rec order by id asc
                            ) as re 
                    ),'/')||'/') as "namepath"
    FROM t_org r ORDER BY id