PostgreSQL数据库中，使用存储过程实现自关联表的树形递归遍历。
本来想用上一篇文章中说的方法，强行在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下第一种报错。
页:
[1]