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
?
作用：對于查詢遞歸樹是個好方法
)
)
