PostgreSQL数据库中,使用存储过程实现自关联表的树形递归遍历。
程序员文章站
2022-05-29 08:18:53
...
本来想用上一篇文章 中说的方法,强行在mysql上实现的,无奈还是太麻烦,相当一部分SQL需要改造,由于是新产品,干脆来个釜底抽薪,换PostgreSQL得了!
由于现在PostgreSQL用的人少,不想这么优秀的数据库被大家(尤其中国的程序员)忽视,把自己的一点心得贡献一下,希望能对PostgreSQL推广起点作用吧!
废话不说,看代码吧(测试表、测试数据都包含了,你准备好psql环境,直接执行就能看到效果):
另:编写的这四个函数,原则是尽量通用,因此把表名和自关联字段名作为参数传递进来。
-- postgresql 8.3 -- -- 测试用表和数据 -- DROP TABLE IF EXISTS test_tree; CREATE TABLE test_tree ( id BIGINT NOT NULL PRIMARY KEY , name VARCHAR(64), description VARCHAR(2048), parent_id BIGINT REFERENCES test_tree(id) ); -- 01-02-04-10 -- -11 -- -05-07 -- -08 -- -03-06-09 -- -12 INSERT INTO test_tree VALUES (1, '名字1', '描述1', null ); INSERT INTO test_tree VALUES (2, '名字2', '描述2', 1 ); INSERT INTO test_tree VALUES (3, '名字3', '描述3', 1 ); INSERT INTO test_tree VALUES (4, '名字4', '描述4', 2 ); INSERT INTO test_tree VALUES (5, '名字5', '描述5', 2 ); INSERT INTO test_tree VALUES (6, '名字6', '描述6', 3 ); INSERT INTO test_tree VALUES (7, '名字7', '描述7', 5 ); INSERT INTO test_tree VALUES (8, '名字8', '描述8', 5 ); INSERT INTO test_tree VALUES (9, '名字9', '描述9', 6 ); INSERT INTO test_tree VALUES (10, '名字10', '描述10', 4 ); INSERT INTO test_tree VALUES (11, '名字11', '描述11', 4 ); INSERT INTO test_tree VALUES (12, '名字12', '描述12', 6 ); CREATE LANGUAGE PLPGSQL; -- -- 自关联表的向下递归 -- DROP FUNCTION IF EXISTS all_progeny_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT); CREATE OR REPLACE FUNCTION all_progeny_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) RETURNS SETOF BIGINT AS $PROC$ DECLARE record_child RECORD; -- 直接子记录 record_childs_progeny RECORD; -- 直接子记录的后代 BEGIN -- 遍历顺序:深度优先;输出顺序:父在前,子在后 FOR record_child IN EXECUTE $$SELECT t.$$ || pkFieldName || $$ AS pk FROM $$ || tableName || $$ AS t WHERE t.$$ || parentPkFieldName || $$ = '$$ || thisPk || $$' ORDER BY t.$$ || pkFieldName LOOP RETURN NEXT record_child.pk; FOR record_childs_progeny IN SELECT * FROM all_progeny_pk(tableName, pkFieldName, parentPkFieldName, record_child.pk) AS pk LOOP RETURN NEXT record_childs_progeny.pk; END LOOP; END LOOP; RETURN; END; $PROC$ LANGUAGE PLPGSQL; COMMENT ON FUNCTION all_progeny_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) IS '查找某张自关联表中,某记录的所有子孙的主键(不包含自己)'; select * from all_progeny_pk('test_tree', 'id', 'parent_id', 1) AS pk; DROP FUNCTION IF EXISTS all_progeny_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT); CREATE OR REPLACE FUNCTION all_progeny_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) RETURNS SETOF BIGINT AS $PROC$ DECLARE record_progeny RECORD; BEGIN RETURN NEXT thisPk; FOR record_progeny IN SELECT * FROM all_progeny_pk(tableName, pkFieldName, parentPkFieldName, thisPk) AS pk LOOP RETURN NEXT record_progeny.pk; END LOOP; RETURN; END; $PROC$ LANGUAGE PLPGSQL; COMMENT ON FUNCTION all_progeny_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) IS '查找某张自关联表中,某记录的所有子孙的主键(包含自己)'; select * from all_progeny_pk_with_self('test_tree', 'id', 'parent_id', 1) AS pk; -- -- 自关联表的向上递归 -- DROP FUNCTION IF EXISTS all_ancestor_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT); CREATE OR REPLACE FUNCTION all_ancestor_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) RETURNS SETOF BIGINT AS $PROC$ DECLARE record_parent RECORD; -- 直接父记录 record_parents_ancestor RECORD; -- 直接父记录的祖先 BEGIN -- 输出顺序:父在前,子在后 FOR record_parent IN EXECUTE $$SELECT t.$$ || parentPkFieldName || $$ AS pk FROM $$ || tableName || $$ AS t WHERE t.$$ || pkFieldName || $$ = '$$ || thisPk || $$' ORDER BY t.$$ || pkFieldName LOOP IF record_parent.pk IS NOT NULL THEN FOR record_parents_ancestor IN SELECT * FROM all_ancestor_pk(tableName, pkFieldName, parentPkFieldName, record_parent.pk) AS pk LOOP RETURN NEXT record_parents_ancestor.pk; END LOOP; RETURN NEXT record_parent.pk; END IF; END LOOP; RETURN; END; $PROC$ LANGUAGE PLPGSQL; COMMENT ON FUNCTION all_ancestor_pk(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) IS '查找某张自关联表中,某记录的所有祖先的主键(不包含自己)'; select * from all_ancestor_pk('test_tree', 'id', 'parent_id', 12) AS pk; DROP FUNCTION IF EXISTS all_ancestor_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT); CREATE OR REPLACE FUNCTION all_ancestor_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) RETURNS SETOF BIGINT AS $PROC$ DECLARE record_ancestor RECORD; BEGIN FOR record_ancestor IN SELECT * FROM all_ancestor_pk(tableName, pkFieldName, parentPkFieldName, thisPk) AS pk LOOP RETURN NEXT record_ancestor.pk; END LOOP; RETURN NEXT thisPk; RETURN; END; $PROC$ LANGUAGE PLPGSQL; COMMENT ON FUNCTION all_ancestor_pk_with_self(tableName VARCHAR, pkFieldName VARCHAR, parentPkFieldName VARCHAR, thisPk BIGINT) IS '查找某张自关联表中,某记录的所有祖先的主键(包含自己)'; select * from all_ancestor_pk_with_self('test_tree', 'id', 'parent_id', 12) AS pk;
注意:调用函数时,可以使用 "select funciton();" 也可以写 "select * from function()" 在linux上无问题,windows下第一种报错。
下一篇: 安装Sourcetree遇到的问题