postgresql中实现oracle SYS_CONNECT_BY_PATH
oracle：
select sys_connect_by_path(t2.ID, '/') as PATH, t1.id
from HTD_DEVICETYPE_RELATION t1
inner join HTD_DEVICETYPE_RESOURCE t2 on t1.CHILD_RESOURCE_TYPE_ID = t2.ID
start with t1.parent_resource_type_id = 100 and t1.id > 1 connect by nocycle
prior t2.id = t1.parent_resource_type_id order by t1.id
postgresql：
with recursive r_table(path,cycle,t1id,t2id,parent_resource_type_id) as
(
select array[t.t2id],false,t.t1id,t.t2id,parent_resource_type_id from
(select t1.id as t1id,t2.id as t2id,t1.parent_resource_type_id as parent_resource_type_id
from HTD_DEVICETYPE_RELATION as t1
inner join HTD_DEVICETYPE_RESOURCE as t2 on
t1.CHILD_RESOURCE_TYPE_ID = t2.ID where t1.parent_resource_type_id = 100 and t1.id > 1) as t
union all
select path || b.t2id,b.t2id = any(path),b.t1id,b.t2id,b.parent_resource_type_id
from r_table
inner join
(select t1.id as t1id,t2.id as t2id,t1.parent_resource_type_id as parent_resource_type_id
from HTD_DEVICETYPE_RELATION as t1
inner join HTD_DEVICETYPE_RESOURCE as t2 on
t1.CHILD_RESOURCE_TYPE_ID = t2.ID) as b
on r_table.t2id = b.parent_resource_type_id and not cycle
)select '/' || Array_to_string(path,'/'),t1id from r_table where not cycle order by t1id;
?column? | t1id
--------------------------+------
/31 | 9
/31/32 | 10
/102 | 1519
/103 | 1520
/104 | 1521
/105 | 1522
/102/115 | 1523
/102/160 | 1524
/102/103/115 | 1526
/103/115 | 1526
/103/160 | 1527
/102/103/160 | 1527
/102/103/113 | 1528
/103/113 | 1528
/104/160 | 1530
/102/104/160 | 1530
/102/105/115 | 1531
/105/115 | 1531
/102/105/160 | 1532
/105/160 | 1532
/102/105/113 | 1533
/105/113 | 1533
/484 | 1534
/491 | 1554
/102/104/114 | 1556
/104/114 | 1556
/185 | 1596
/154 | 1614
/493 | 1634
/504 | 1635
--More--
实例中通过array数值保存访问过的id，b.t2id = any(path)检查是否已经访问过来避免产生死循环
在递归查询时，如出现如下错误
是由于数据库表字段类型numeric(20,0)不支持with递归查询，将数据库表字段改为bigint即可。
