pgsql中层次查询方法-例子
求序列和
PostgreSQL 官方文档用求序列和的例子，演示了 WITH RECURSIVE 的用法：
postgres=# WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
sum
------
5050
(1 row)
公交换乘问题
实际的，工业强度的换乘算法肯定会更多地利用地理相关的信息做剪枝操作。这里给出的例子仅仅为了解释 WITH RECURSIVE 而提供。欢迎有过实现经验的朋友，分享更好的方法。
基础数据
从网上找一份北京市公共交通线路表，导入数据库。 最基础的数据表格可以是这样：
postgresql=# SELECT * from bj_bus_table limit 3;
id | line | s_from | s_to
----+------+----------+----------
1 | 1路 | 马官营 | 六里桥北
2 | 1路 | 六里桥北 | 马官营
3 | 1路 | 马官营 | 公主坟南
(3 rows)
postgresql=# SELECT count(*) from bj_bus_table;
count
--------
671420
(1 row)
这张表格的基础是我们认为同一条路线上的所有站点，都是直接可达的。
简单又粗暴的解法
我们的问题是从 A 到 B 如何走。根据“简单粗暴”的思考原则，我们：
列出所有从A开始的线路
从列表中选出终点为B的
拜首都强大的交通网络所赐，这个简单粗暴的解法仅能满足1次换乘的需求。一旦牵涉到2次换乘，将会消耗上G的内存，运行10数分钟以上，并且毫无结果。
SQL 如下：
CREATE OR REPLACE FUNCTION bj_bus_exchange_simple(
source text, dest text,
s_from OUT text, s_to OUT text, station_path OUT text[],
line_path OUT text[], s_level OUT integer )
RETURNS setof record AS $$
WITH RECURSIVE bus_path (s_from, s_to, station_path, line_path, level) AS
( select s_from , s_to, ARRAY[s_from, s_to] as station_path,
ARRAY[line] as line_path , 1
from bj_bus_table b1
where b1.s_from = $1
union all
select b2.s_from, b2.s_to,
bus_path.station_path || b2.s_to,
bus_path.line_path || b2.line,
bus_path.level + 1
from bj_bus_table b2
join bus_path
on (
level < 2
and
bus_path.s_to = b2.s_from
and
b2.line <> ANY(bus_path.line_path)
)
)
select * from bus_path where s_to = $2
$$ LANGUAGE 'SQL';
来一个简单的测试
postgresql=# SELECT * from bj_bus_exchange_simple('天安门西', '红庙')
order by random() limit 8;
s_from | s_to | station_path | line_path | s_level
--------+------+------------------------+---------------------+---------
平乐园 | 红庙 | {天安门西,平乐园,红庙} | {52路上行,30路上行} | 2
牡丹园 | 红庙 | {天安门西,牡丹园,红庙} | {726路上行,749路} | 2
梆子井 | 红庙 | {天安门西,梆子井,红庙} | {728路,976路支线} | 2
日坛路 | 红庙 | {天安门西,日坛路,红庙} | {728路,640路上行} | 2
八王坟 | 红庙 | {天安门西,八王坟,红庙} | {1路,976路支线} | 2
八王坟 | 红庙 | {天安门西,八王坟,红庙} | {4路,973路} | 2
清华园 | 红庙 | {天安门西,清华园,红庙} | {726路上行,749路} | 2
四惠站 | 红庙 | {天安门西,四惠站,红庙} | {728路,976路支线} | 2
(8 rows)
postgresql=# SELECT count(*) from bj_bus_exchange_simple('天安门西', '红庙');
count
-------
60
(1 row)
很明显，梆子井 这个站并不适合出现在这里。这样的路线等于让乘客走了倒路。这种情况下，我们给每个站加入经纬度坐标，再加上合适的判断语句（这就是传说中的剪枝），就能避免这种情况的出现。
更多例子：
http://forums.devshed.com/postgresql-help-21/recursive-sql-query-101259.html
http://explainextended.com/2009/09/24/adjacency-list-vs-nested-sets-postgresql/
http://explainextended.com/2010/03/01/postgresql-nested-sets-and-r-tree/
http://explainextended.com/2010/03/02/postgresql-using-recursive-functions-in-nested-sets/
转：http://www.pgsqldb.org/mwiki/index.php/PostgreSQL_In_%E2%80%94%E2%80%94_RECURSIVE
