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 上建立单独的索引。