PostgreSQL是否有伪列，例如"LEVEL"?在甲骨文? [英] Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?

问题描述

PostgreSQL在Oracle中是否有伪列，例如"LEVEL"?
如果没有，那么我们如何创建类似于"LEVEL"的列?

Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
If not, then how can we create a column similar to "LEVEL"?

推荐答案

Postgres没有分层查询.没有CONNECT BY，因此也没有LEVEL.

Postgres does not have hierarchical queries. No CONNECT BY, therefore also no LEVEL.

附加模块tablefunc提供功能 connectby() 几乎相同.参见:

The additional module tablefunc provides the function connectby() doing almost the same. See:

• 什么Oracle的CONNECT BY ... START WITH?等效的PostgreSQL语法是什么?

或者您也可以使用标准递归CTE 和level列，该列随每次递归递增.
Oracle中的此查询:

Or you can do similar things with a standard recursive CTE and a level column that's incremented with every recursion.
This query in Oracle:

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

..可以在Postgres中转换为该递归CTE:

.. can be translated to this recursive CTE in Postgres:

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;

这篇关于PostgreSQL是否有伪列，例如"LEVEL"?在甲骨文?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！