在PostgreSQL下迭代实现递归查询

前言:

考虑这样一个问题，机构A的上级机构是B，机构B的上级机构是C，机构C的上级机构是D，机构D的上级机构是E. 现在我们需要找到给定机构的所有上级机构（例如E，例如A）.

此问题的典型表结构如下

机构（ins_id）

上级院校（pre_ins_id）

今天，我们只想解决这些问题.

以下函数将in_ins_id作为参数递归查询，计算并返回该机构的所有直接或间接上级机构，并返回其中的一组

CREATE FUNCTION getAllPreIns(VARCHAR)	--接收一个VARCHAR参数
 RETURNS TABLE(ins_id VARCHAR)	--返回一个table
 LANGUAGE plpgsql
AS $function$	--function标识
DECLARE in_ins_id ALIAS FOR $1;    --把接收的的参数放在in_ins_id变量中
BEGIN
	CREATE TEMPORARY TABLE ins(ins_id VARCHAR(8));	--该临时表将返回最终的数据	
	CREATE TEMPORARY TABLE new_ins(ins_id VARCHAR(8)); --存储前一次迭代中找到的数据
	CREATE TEMPORARY TABLE temp(ins_id VARCHAR(8));	--用于存放对集合操作时的中间数据
	--先把in_ins_id的上级机构插入new_ins表中
	INSERT INTO new_ins
		SELECT pre_ins_id
		FROM ins_table
		WHERE ins_table.ins_id = in_ins_id;
	LOOP
		--将new_ins中的数据插入到ins中
		INSERT INTO ins
			SELECT new_ins.ins_id
			FROM new_ins;
		--将new_ins中ins_id的上级机构插入到temp中
		INSERT INTO temp
			(SELECT ins_table.pre_ins_id
				FROM new_ins, ins_table
				WHERE new_ins.ins_id = ins_table.ins_id)
			EXCEPT	--防止出现机构环如A的上级机构是B，B的上级机构是C，C的上级机构是A
			(SELECT ins.ins_id
				FROM ins);
		DELETE FROM new_ins;	--清空new_ins
		INSERT INTO new_ins		--保存temp
			SELECT temp.ins_id
			FROM temp;
		DELETE FROM temp;
		EXIT WHEN NOT EXISTS (SELECT new_ins.ins_id FROM new_ins);	--知道没有上级机构市循环终止
	END LOOP;
	RETURN QUERY SELECT ins.ins_id FROM ins;	--返回
END;
$function$	--function标识

假设我们传入的参数是A. 在此函数中，我们首先找到A的上级机构B，并将其存储在new_ins中. 这个循环一直持续到在特定循环中没有添加新机构为止.

此过程中使用了4张桌子

SELECT ins_id, pre_ins_id FROM ins_table; 

在LOOP循环之前，我们首先将in_ins_id的所有直接上级机构插入表new_ins中. LOOP循环从首先将new_ins中的所有机构插入ins开始. 然后为new_ins中的所有机构计算父机构递归查询，并删除之前已计算的in_ins_id的父机构，并将其余机构插入到temp中. 最后，用临时数据替换new_ins. 当new_ins为空时，LOOP终止.

最后我们在postgresql中调用此函数

SELECT * FROM getAllPreIns('A');

结果如下

结束语: 以上所有SQL语句均通过PostgreSQL运行. 由于各种的语法不同，因此不能保证在其他中不会报告任何错误. 由于本人水平的限制，如有错误，欢迎提出批评和指正.

参考文献:

1. 第六版“系统概念”的第5章: 高级SQL

2. （en）

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-174051-1.html