Oracle迁移PostgreSQL系列文章之一:connect_by

Posted on 2015-03-06 11:26:34 by osdba

PostgreSQL不支持Oracle中的connect by语法,而即使是edb对 connect by的兼容有限,edb不支持的点:

  • 函数 sys_connect_by_path
  • 在SELECT表达式中使用PRIOR限定符
  • CONNECT BY 有多个表达式
  • CONNECT_BY_ROOT 表达式

Oracle中还有以下:

  • connect_by_is_leaf :connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
  • connect_by_iscycle: connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

解决方案如下,

假设Oracle中有表:

CREATE TABLE sys_cbp_test
  (id INTEGER NOT NULL PRIMARY KEY,parent_id INTEGER
  );

生成测试数据:

INSERT INTO sys_cbp_test
SELECT 1, NULL FROM dual
UNION ALL
SELECT 2, 1 FROM dual
UNION ALL
SELECT 3, 2 FROM dual
UNION ALL
SELECT 4, 3 FROM dual
UNION ALL
SELECT 5, 1 FROM dual
UNION ALL
SELECT 6, 5 FROM dual
UNION ALL
SELECT 7, 2 FROM dual
UNION ALL
SELECT 20, NULL FROM dual
UNION ALL
SELECT 21, 20 FROM dual
UNION ALL
SELECT 22, 21 FROM dual;

表的内容如下:

SQL> select * from sys_cbp_test;

 ID  PARENT_ID
--- ----------
  1
  2      1
  3      2
  4      3
  5      1
  6      5
  7      2
 20
 21     20
 22     21

10 rows selected.

Oracle中的查询语句为:

SELECT id,
  prior id,
  parent_id,
  Level,
  sys_connect_by_path (TO_CHAR (id), '/') AS Path,
  CONNECT_BY_ROOT  id AS root
FROM sys_cbp_test
  START WITH parent_id   IS NULL
  CONNECT BY prior id = parent_id;

Oracle中查询的结果如下:

 ID    PRIORID  PARENT_ID      LEVEL PATH       ROOT
--- ---------- ---------- ---------- ------------ ----------
  1                1 /1            1
  2      1      1      2 /1/2          1
  3      2      2      3 /1/2/3        1
  4      3      3      4 /1/2/3/4          1
  7      2      2      3 /1/2/7        1
  5      1      1      2 /1/5          1
  6      5      5      3 /1/5/6        1
 20                1 /20          20
 21     20         20      2 /20/21       20
 22     21         21      3 /20/21/22        20

10 rows selected.

PostgreSQL中生成测试数据的SQL如下:

INSERT INTO sys_cbp_test VALUES (1, NULL),(2, 1),(3, 2),(4, 3),(5, 1),(6, 5),(7, 2),(20, NULL),(21, 20),(22, 21);
PostgreSQL中的SQL如下:

WITH RECURSIVE x (id, prior_id, parent_id, level, path, root) AS
(SELECT id, NULL::INT AS prior_id, NULL::INT AS parent_id, 1, array[id], id as root
   FROM sys_cbp_test
  WHERE parent_id IS NULL
  UNION ALL
 SELECT b.id, x.id AS prior_id, b.parent_id, level+1, x.path|| b.id, x.root
   FROM x, sys_cbp_test b
  WHERE x.id = b.parent_id
)
SELECT id, prior_id, parent_id, level, '/'|| array_to_string (path, '/') AS path, root
  FROM x;

PostgreSQL看到的结果如下:

 id | prior_id | parent_id | level |   path    | root
----+----------+-----------+-------+-----------+------
  1 |          |           |     1 | /1        |    1
 20 |          |           |     1 | /20       |   20
  2 |        1 |         1 |     2 | /1/2      |    1
  5 |        1 |         1 |     2 | /1/5      |    1
 21 |       20 |        20 |     2 | /20/21    |   20
  3 |        2 |         2 |     3 | /1/2/3    |    1
  6 |        5 |         5 |     3 | /1/5/6    |    1
  7 |        2 |         2 |     3 | /1/2/7    |    1
 22 |       21 |        21 |     3 | /20/21/22 |   20
  4 |        3 |         3 |     4 | /1/2/3/4  |    1
(10 rows)

行的顺序与Oracle不一样,但对于关系型数据库一般是不保证行的顺序的,如果要保证,需要排序,如:

WITH RECURSIVE x (id, prior_id, parent_id, level, path, root) AS
(SELECT id, NULL::INT AS prior_id, NULL::INT AS parent_id, 1, array[id], id as root
   FROM sys_cbp_test
  WHERE parent_id IS NULL
  UNION ALL
 SELECT b.id, x.id AS prior_id, b.parent_id, level+1, x.path|| b.id, x.root
   FROM x, sys_cbp_test b
  WHERE x.id = b.parent_id
)
SELECT id, prior_id, parent_id, level, '/'|| array_to_string (path, '/') AS path, root
  FROM x
 ORDER BY id NULLS FIRST
  ;

 id | prior_id | parent_id | level |   path    | root
----+----------+-----------+-------+-----------+------
  1 |          |           |     1 | /1        |    1
  2 |        1 |         1 |     2 | /1/2      |    1
  3 |        2 |         2 |     3 | /1/2/3    |    1
  4 |        3 |         3 |     4 | /1/2/3/4  |    1
  5 |        1 |         1 |     2 | /1/5      |    1
  6 |        5 |         5 |     3 | /1/5/6    |    1
  7 |        2 |         2 |     3 | /1/2/7    |    1
 20 |          |           |     1 | /20       |   20
 21 |       20 |        20 |     2 | /20/21    |   20
 22 |       21 |        21 |     3 | /20/21/22 |   20
(10 rows)