PostgreSQL：递归查询应用场景

摘要：今天在坛子里有人提出了一个问题，问题是这样的在以下指定表中中国辽宁山东沈阳大连济南和平区沈河区现在给定一个号，想得到它完整的名字。递归往上找，直到为止。也就是最高层级时结束，求完整语句。

今天在坛子里有人提出了一个问题，问题是这样的：在以下指定表中

id name fatherid
1 中国 0
2 辽宁 1
3 山东 1
4 沈阳 2
5 大连 2
6 济南 3
7 和平区 4
8 沈河区 4

现在给定一个id号，想得到它完整的名字。如：
当id=7时，名字是：中国辽宁沈阳和平区
当id=5时，名字是：中国辽宁大连

id是任意给定的，不确定在哪一层。递归往上找，直到 fatherid=0 为止。也就是最高层级时结束，
求完整SQL语句。

看到这个问题，第一想到的是可以用 PG的递归查询实现，之前也写过类似的例子，
http://francs3.blog.163.com/b...，但之前的例子
是向下递归，而这里的需求是向上递归，略有不同，于是忍不住演示下：

这个问题的思路是分两步走，第一步：查询出指定节点的父节点；第二步：将查询出的所有父节点排列到一行。

--1 创建测试表，并插入测试数据

skytf=> create table test_area(id int4,name varchar(32),fatherid int4);
CREATE TABLE

insert into test_area values (1, "中国"   ,0);
insert into test_area values (2, "辽宁"   ,1);
insert into test_area values (3, "山东"   ,1);
insert into test_area values (4, "沈阳"   ,2);
insert into test_area values (5, "大连"   ,2);
insert into test_area values (6, "济南"   ,3);
insert into test_area values (7, "和平区" ,4);
insert into test_area values (8, "沈河区" ,4);
skytf=> select * From test_area;
 id |  name  | fatherid 
----+--------+----------
  1 | 中国   |        0
  2 | 辽宁   |        1
  3 | 山东   |        1
  4 | 沈阳   |        2
  5 | 大连   |        2
  6 | 济南   |        3
  7 | 和平区 |        4
  8 | 沈河区 |        4
(8 rows)

--2 查询指定节点以下的所有节点

  WITH RECURSIVE r AS ( 

       SELECT * FROM test_area WHERE id = 4 
     union   ALL 
       SELECT test_area.* FROM test_area, r WHERE test_area.fatherid = r.id 
     ) 
SELECT * FROM r ORDER BY id;
 id |  name  | fatherid 
----+--------+----------
  4 | 沈阳   |        2
  7 | 和平区 |        4
  8 | 沈河区 |        4
(3 rows)

备注：通常的用法是查询指定节点以及指定节点以下的所有节点，那么本贴的需求刚好相反，需要查询指定节点以上的所有节点。

--3 查询指定节点以上的所有节点

 WITH RECURSIVE r AS ( 
       SELECT * FROM test_area WHERE id = 4 
     union   ALL 
       SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid 
     ) 
 SELECT * FROM r ORDER BY id;

 id | name | fatherid 
----+------+----------
  1 | 中国 |        0
  2 | 辽宁 |        1
  4 | 沈阳 |        2
(3 rows)

备注：这正是我们想要的结果，接下来需要将 name 字段结果集合并成一行，我这里想到的是创建个 function，当然也有其它方法。

--4 create funcion

CREATE or replace FUNCTION func_get_area(in in_id int4, out o_area text)  AS 

$$ 
DECLARE
   v_rec_record RECORD;
BEGIN

  o_area = "";
  FOR v_rec_record IN (WITH RECURSIVE r AS (SELECT *
                           FROM test_area
                          WHERE id = in_id
                         union ALL
                         SELECT test_area.*
                           FROM test_area, r
                          WHERE test_area.id = r.fatherid)SELECT name
                         FROM r
                        ORDER BY id) LOOP
    o_area := o_area || v_rec_record.name;
  END LOOP;
  return;
END; 
$$
LANGUAGE "plpgsql";

备注：函数的作用为拼接 name 字段。

--5 测试

 skytf=> select func_get_area(7) ;
   func_get_area    
--------------------
 中国辽宁沈阳和平区
(1 row)

skytf=> select func_get_area(5) ;
 func_get_area 
---------------
 中国辽宁大连
(1 row)

备注：正好实现了需求，当表数据量较大时，考虑到性能，建议在表 test_area 字段 id，fatherid 上建立多带带的索引。