PostgreSQL Oracle 兼容性 - connect by 2

3 minute read

背景

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)  

参考

《PostgreSQL 家谱、族谱类应用实践 - 图式关系存储与搜索》

《PostgreSQL 递归妙用案例 - 分组数据去重与打散》

《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》

《PostgreSQL 图式搜索(graph search)实践 - 百亿级图谱,毫秒响应》

《[未完待续] AgensGraph 图数据库介绍以及 on ECS部署》

《PostgreSQL 实践 - 内容社区(如论坛)图式搜索应用》

《[未完待续] PostgreSQL 图计算》

《小微贷款、天使投资(风控助手)业务数据库设计(图式搜索\图谱分析) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践》

《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》

《PostgreSQL 递归查询CASE - 树型路径分组输出》

《金融风控、公安刑侦、社会关系、人脉分析等需求分析与数据库实现 - PostgreSQL图数据库场景应用》

《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》

《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

《时序数据合并场景加速分析和实现 - 复合索引,窗口分组查询加速,变态递归加速》

《facebook linkbench 测试PostgreSQL社交关系图谱场景性能》

《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》

《PostgreSQL 递归死循环案例及解法》

《PostgreSQL 递归查询一例 - 资金累加链》

《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》

《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》

Flag Counter

digoal’s 大量PostgreSQL文章入口