PostgreSQL 中的递归查询 与oracle 的比较

PostgreSQL 中的递归查询，2种方法：

1、用with decursive

WITH RECURSIVE d AS (SELECT d1.id,d1.parent_id,d1.caption FROM course_types d1 where d1.dr = 0 and d1.id='typeId' union ALL SELECT d2.id,d2.parent_id,d2.caption FROM course_types d2, d WHERE d2.dr = 0 and d2.parent_id = d.id) SELECT * FROM d

其中typeId 是初始id

2、用递归函数

CREATE OR REPLACE FUNCTION query_child_dept_auth(character varying, lv1 integer)
  RETURNS SETOF w_help_dept AS
$BODY$  
DECLARE  
itemid ALIAS FOR $1;  
itemrecord record;  
BEGIN  
    SELECT s.*,lv1 as lv INTO itemrecord FROM depts s  where s.dr = 0 and s.state = 1 and id=itemid order by s.sort,convert_to(s.caption,'gbk');  
    RETURN NEXT itemrecord;  
     IF (select count(1) from depts s where s.dr = 0 and s.state = 1 and s.parent_id=itemrecord.id) >0  THEN  
            for itemrecord in SELECT s.* FROM depts s  where s.dr = 0 and s.state = 1 and s.parent_id=itemrecord.id order by s.sort,convert_to(s.caption,'gbk') LOOP  
                for itemrecord in select * from query_child_dept_auth (itemrecord.id,(lv1+1)) LOOP  
                    RETURN NEXT itemrecord;  
                end LOOP;  
            end LOOP;  
     END IF;  
    RETURN;
END;$BODY$
  LANGUAGE plpgsql VOLATILE

其中w_help_dept是辅助表，表字段和depts一样，且多个lv字段，lv字段用于模拟oracle递归查询中的level树层数

 

oracle 递归查询

网上资料蛮多，随便找了个http://cpdw.iteye.com/blog/625574

 

作用：对于查询递归树是个好方法