PostgreSQL有像Oracle中的“LEVEL”一样的伪列吗？

Question

PostgreSQL有类似Oracle中的“LEVEL”伪列吗？
如果没有，那么我们如何创建类似于“LEVEL”的列？

- Neha

Answer 1

PostgreSQL没有分层查询。没有CONNECT BY，因此也没有LEVEL。

附加模块提供了函数connectby()几乎实现同样的功能。详情请参见mthorley answer。

或者您可以使用标准递归公共表达式（CTE）及一个在每次递归时增加的level列。

在Oracle中，使用以下查询：

SELECT employee_id, last_name, manager_id, LEVEL
FROM   employees
CONNECT BY PRIOR employee_id = manager_id;

..可以在Postgres中使用这个递归CTE进行翻译：

WITH RECURSIVE cte AS (
   SELECT employee_id, last_name, manager_id, 1 AS level
   FROM   employees

   UNION  ALL
   SELECT e.employee_id, e.last_name, e.manager_id, c.level + 1
   FROM   cte c
   JOIN   employees e ON e.manager_id = c.employee_id
   )
SELECT *
FROM   cte;

- Erwin Brandstetter

---

是的。CONNECT BY 对于它可以表达的内容更加清晰简洁，但比WITH RECURSIVE灵活性要差得多。 - Craig Ringer

Answer 2

是的，Postgres支持像Oracle一样的“LEVEL”。

但是，正如其他答案所指出的那样，您必须加载 tablefunc 扩展。

如果您拥有管理访问权限，则可以使用以下命令加载它： CREATE EXTENSION IF NOT EXISTS tablefunc; 有关更多信息，请查看文档： https://www.postgresql.org/docs/current/static/tablefunc.html 这是我们应用程序中一个真实的connectby的例子。我们正在使用它来查找通过其报告树向上汇报给经理的所有人。

SELECT system_user.system_user_id
    , system_user.first_name
    , system_user.last_name
    , team.mgr_id as managers_system_user_id
    , team.level
    , team.hierarchy
    FROM connectby('system_user_manager_rltnp'
                   , 'system_user_id'
                   , 'system_users_managers_id'
                   , 2963049 -- the users system_user_id
                   , 5       -- the max levels of depth
                   , '~')    -- the hierarchy delimiter
    AS team(rpt_id numeric, mgr_id numeric, level int, hierarchy text),
    system_user
    WHERE team.rpt_id = system_user.system_user_id

它将返回以下类似的结果。在此您可以看到级别，以及整个层次结构作为字符串。

"system_user_id","first_name","last_name","managers_system_user_id","level","hierarchy"
"2963049","Debbie","Buswell","",0,"2963049"
"2963045","Linda","Simply","2963049",1,"2963049~2963045"
"2963047","Cindy","Brouillard","2963049",1,"2963049~2963047"
"2963048","Sharon","Burns","2963049",1,"2963049~2963048"
"2963050","Marie-Eve","Casper","2963049",1,"2963049~2963050"
"2963051","Tammy","Cody","2963049",1,"2963049~2963051"

- mthorley

Answer 3

使用Connect By、Starts With和level指示器的功能，如果您在postgres中启用tablefunc扩展，即可使用。 语法略有不同，但如果您了解来自oracle的connect by，则大约90秒后就可以掌握此功能。 当我将一个oracle系统转换为postgres系统时，这个功能非常好用，拯救了我的生命。

我已经在类似的问题中给出了所有细节。
Stackoverflow Connect By answer

- Stradas