PostgreSQL下迭代实现数据库递归查询

前言：

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

这种问题的典型表结构如下

机构(ins_id)	上级机构(pre_ins_id)
A	B
B	C
C	D
D	E

 

今天我们就是要解决诸如此类的问题。

下面的函数以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，在此function中，我们先找到A的上级机构B并存放在new_ins中，如此循环，直到某次循环中没有新的机构加进来才停止。

 

此过程所用到的4个表

• ins：存储要返回的元素集合
• new_ins：存储每一次迭代中找到的上级机构，并在下一次迭代中插入到表ins中
• temp：临时存储每一次迭代中找到的上级机构
• Ins_table：我们的原始机构表（如下图）

 

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中。最后用temp的数据替换new_ins。当new_ins为空时，LOOP终止。

 

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

 

SELECT * FROM getAllPreIns('A');

 结果如下图

 

 

结束语：以上的SQL语句全部是在PostgreSQL中运行通过，由于各种数据库的语法的差别，在其他数据库中并不能保证不会报错。由于本人水平所限，如有错误，欢迎批评指正。

 

参考文献：

1. 《数据库系统概念》第六版第五章：高级SQL

2. http://postgres.cz/wiki/PL/pgSQL_(en)

 

 

版权声明：本文为博主原创文章，未经博主允许不得转载。