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下第一种报错。