PostgreSQL Oracle 兼容性 - connect by 2-阿里云开发者社区

PostgreSQL Oracle 兼容性 - connect by 2

2018-10-05 2847

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB 分布式版，标准版 2核8GB

RDS PostgreSQL Serverless，0.5-4RCU 50GB 3个月

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

简介：

背景

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)