PostgreSQL 透過 WITH RECURSIVE(Common Table Expression)原生支援遞迴查詢,語法直觀、效能優異,是處理鄰接模型樹狀資料的首選方案。
-- country(id, name)
-- country_relation(id, parentid)
WITH RECURSIVE tmp_country(id, path) AS (
-- 錨點查詢:根節點
SELECT a.id, '/' || b.name AS path
FROM country_relation a
INNER JOIN country b ON a.id = b.id
WHERE a.parentid IS NULL
UNION ALL
-- 遞迴查詢:子節點接在父路徑後
SELECT a.id, q.path || '/' || b.name
FROM country_relation a
INNER JOIN tmp_country q ON q.id = a.parentid
INNER JOIN country b ON a.id = b.id
)
SELECT path FROM tmp_country ORDER BY path;
輸出(16 列):
/Earth
/Earth/North America
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia
耗時約 3ms,效能極佳。
需先啟用擴充:CREATE EXTENSION tablefunc;
因 connectby 需要「名稱+父名稱」欄位,先建暫存表並填入父名稱:
CREATE TEMPORARY TABLE tmp_country_relation AS
SELECT b.id, a.name, b.parentid, ''::text AS parentname
FROM country a, country_relation b WHERE a.id = b.id;
UPDATE tmp_country_relation
SET parentname = a.name
FROM country a WHERE parentid = a.id;
SELECT path FROM connectby(
'tmp_country_relation', -- 表名
'name', -- 節點名稱欄位
'parentname', -- 父節點名稱欄位
'Earth', -- 起始根節點
0, -- 最大深度(0=不限)
'/' -- 路徑分隔符
) AS t(id text, parentid text, level int, path text)
ORDER BY level;
輸出(16 列,順序依層級):
Earth
Earth/Australia
Earth/North America
Earth/Africa
Earth/South America
Earth/Europe
Earth/Asia
Earth/North America/Island Nations
Earth/North America/Canada
Earth/North America/Central America
Earth/North America/United States
Earth/North America/United States/California
Earth/North America/United States/Arkansas
Earth/North America/United States/Alabama
Earth/North America/United States/Alaska
Earth/North America/United States/Arizona
耗時約 6ms。
| 維度 | WITH RECURSIVE | tablefunc.connectby |
|---|---|---|
| 相依性 | 核心功能,無需額外安裝 | 需 CREATE EXTENSION tablefunc |
| 彈性 | 極高,可任意組合 JOIN、過濾、排序 | 固定參數介面,較死板 |
| 效能 | 優,查詢優化器支援完善 | 良,但大樹可能較慢 |
| 適用場景 | 通用遞迴查詢、複雜業務邏輯 | 快速原型、相容 Oracle 遷移專案 |
優先使用 WITH RECURSIVE,符合 SQL 標準、可維護性高;僅在需快速相容 Oracle CONNECT BY 語義時考慮 tablefunc。