oracle level connect by 在postgresql中的写法

今天工作被一个递归嵌套卡住了，废话不多说，代码如下：

select t.*
		from (
		    select cporg.code as lid,
			cporg.name as level_name,
			cporg.SRD_CODE as parent_id,
			cporg.type_flag as type_flag
			from (
				with recursive cte as(
					select x.* from gf_sys_organization x
					where x.code = '10520301'
					union all
					select y.* from gf_sys_organization y
					inner join cte c on c.code = y.srd_code
					and y.catagory = '1'
				)select code,name,SRD_CODE,1 as type_flag, 'cadre1' icon_cls from cte
       union all
				select cp.id, cp.level_name, cp.ORG_CODE, -1 as type_flag, 'cadre2' icon_cls
        from GF_cadre_position cp
			) cporg
			start with cporg.code = '10520301'
			connect by prior cporg.code = cporg.SRD_CODE
		) t order by t.level_falg, t.type_flag asc

可以清楚地看到是先递归了一次查出结果集，然后再在这个结果集中再递归查询，进行层次标记。

按照正常的思路应该有如下代码：

with recursive ttt as (
	select x.code, x.name, x.SRD_CODE, 1 as type_flag, 'cadre1' icon_cls
								from gf_sys_organization x
								where x.code = '10520301'
								
								union all
								
								
						select m.* from 
						(
							with recursive cte as
							(
								select x.*
								from gf_sys_organization x
								where x.code = '10520301'
								union all
								select y.*
								from gf_sys_organization y
								inner join cte c on c.code = y.srd_code
								and y.catagory = '1'
							) select code, name, SRD_CODE, 1 as type_flag, 'cadre1' icon_cls
							from cte
				
							union all
							select cp.id, cp.level_name, cp.ORG_CODE, -1 as type_flag, 'cadre2' icon_cls from GF_cadre_position cp
							
					)  as m 				
					inner join ttt t on t.code = m.srd_code	
) select code, name, SRD_CODE, type_flag, icon_cls from ttt 

但是并不是这样，它会报错，说递归类型不匹配。我检查了很久，最后猜测递归查出来的结果和我们普通查询查出来的结果，字段类型不匹配，应该是数据库会给递归查询结果集一个专门的数据类型。

经过对需求和表的理解，我修改如下：

select t.*
		from (with recursive cte as
							(
								select x.*, 1 depth
								from 
								
		            (
									select code, name, SRD_CODE, 1 as type_flag, 'cadre1' icon_cls
									from gf_sys_organization 
									where catagory = #{orgStyle,jdbcType=VARCHAR}
									union all
									select cp.id, cp.level_name, cp.ORG_CODE, -1 as type_flag, 'cadre2' icon_cls from GF_cadre_position cp
								
								)	x	
								where x.code = #{orgCode,jdbcType=VARCHAR}
								union all
								select y.*, c.depth + 1 depth
								from (
									select code, name, SRD_CODE, 1 as type_flag, 'cadre1' icon_cls
									from gf_sys_organization 
									where catagory = #{orgStyle,jdbcType=VARCHAR}
									union all
									select cp.id, cp.level_name, cp.ORG_CODE, -1 as type_flag, 'cadre2' icon_cls from GF_cadre_position cp
								
								)	 y
								inner join cte c on c.code = y.srd_code
							) select code as lid,
			name as level_name,
			SRD_CODE as parent_id,
			type_flag as type_flag,
			depth as level_falg, icon_cls
							from cte) t
							order by t.level_falg, t.type_flag asc

以上就是我今天所碰到的问题，不足之处请指正，或者有哪位找到了我报错的原因可以给我留言，谢谢