PostgreSQL Oracle 相容性 - connect by 2

背景

Oracle connect by語法經常用於有樹形關係的記錄查詢，PostgreSQL使用CTE遞迴語法，可以實現同樣的功能。

《PostgreSQL Oracle 相容性之 - connect by 高階選項 CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、CONNECT_BY_ISCYCLE、LEVEL》

本文通過一個更加簡單的方法，同樣可以實現一樣的功能，用到tablefunc外掛中的connectby函式。

介面如下

https://www.postgresql.org/docs/devel/static/tablefunc.html

connectby(text relname, text keyid_fld, text parent_keyid_fld  
          [, text orderby_fld ], text start_with, int max_depth  
          [, text branch_delim ])  
  
setof record	  
  
Produces a representation of a hierarchical tree structure

Parameter	Description
relname	Name of the source relation
keyid_fld	Name of the key field
parent_keyid_fld	Name of the parent-key field
orderby_fld	Name of the field to order siblings by (optional)
start_with	Key value of the row to start at
max_depth	Maximum depth to descend to, or zero for unlimited depth
branch_delim	String to separate keys with in branch output (optional)

例子

create extension tablefunc;  
  
CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);  
  
INSERT INTO connectby_tree VALUES('row1',NULL, 0);  
INSERT INTO connectby_tree VALUES('row2','row1', 0);  
INSERT INTO connectby_tree VALUES('row3','row1', 0);  
INSERT INTO connectby_tree VALUES('row4','row2', 1);  
INSERT INTO connectby_tree VALUES('row5','row2', 0);  
INSERT INTO connectby_tree VALUES('row6','row4', 0);  
INSERT INTO connectby_tree VALUES('row7','row3', 0);  
INSERT INTO connectby_tree VALUES('row8','row6', 0);  
INSERT INTO connectby_tree VALUES('row9','row5', 0);  
  
-- with branch, without orderby_fld (order of results is not guaranteed)  
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')  
 AS t(keyid text, parent_keyid text, level int, branch text);  
 keyid | parent_keyid | level |       branch  
-------+--------------+-------+---------------------  
 row2  |              |     0 | row2  
 row4  | row2         |     1 | row2~row4  
 row6  | row4         |     2 | row2~row4~row6  
 row8  | row6         |     3 | row2~row4~row6~row8  
 row5  | row2         |     1 | row2~row5  
 row9  | row5         |     2 | row2~row5~row9  
(6 rows)  
  
-- without branch, without orderby_fld (order of results is not guaranteed)  
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)  
 AS t(keyid text, parent_keyid text, level int);  
 keyid | parent_keyid | level  
-------+--------------+-------  
 row2  |              |     0  
 row4  | row2         |     1  
 row6  | row4         |     2  
 row8  | row6         |     3  
 row5  | row2         |     1  
 row9  | row5         |     2  
(6 rows)  
  
-- with branch, with orderby_fld (notice that row5 comes before row4)  
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')  
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);  
 keyid | parent_keyid | level |       branch        | pos  
-------+--------------+-------+---------------------+-----  
 row2  |              |     0 | row2                |   1  
 row5  | row2         |     1 | row2~row5           |   2  
 row9  | row5         |     2 | row2~row5~row9      |   3  
 row4  | row2         |     1 | row2~row4           |   4  
 row6  | row4         |     2 | row2~row4~row6      |   5  
 row8  | row6         |     3 | row2~row4~row6~row8 |   6  
(6 rows)  
  
-- without branch, with orderby_fld (notice that row5 comes before row4)  
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)  
 AS t(keyid text, parent_keyid text, level int, pos int);  
 keyid | parent_keyid | level | pos  
-------+--------------+-------+-----  
 row2  |              |     0 |   1  
 row5  | row2         |     1 |   2  
 row9  | row5         |     2 |   3  
 row4  | row2         |     1 |   4  
 row6  | row4         |     2 |   5  
 row8  | row6         |     3 |   6  
(6 rows)