PostgreSQLOracle相容性-connectby2

德哥發表於2018-10-05

SQLOracle

背景

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

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

《PostgreSQL Oracle 相容性之 – connect by》

本文通過一個更加簡單的方法，同樣可以實現一樣的功能，用到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)