如何识别和列出Postgres中的循环引用元素

Question

我有一个员工、经理层次结构，它最终可能是循环的。

例如：28397468N>88518119N>87606705N>28397468N

Create Table emp_manager (  Emp_id varchar(30), Manager_id varchar(30));
Insert into emp_manager    values ('28397468N','88518119N');
Insert into emp_manager    values ('88518119N','87606705N');
Insert into emp_manager    values ('87606705N','28397468N');

我的要求是：当调用我的proc并且emp_manager表中存在循环层次结构时，我们应该返回一个错误，列出层次结构中的雇员。

以下链接包含一些有用的信息：https://mccalljt.io/blog/2017/01/postgres-circular-references/

我修改如下：

select * from (
WITH RECURSIVE circular_managers(Emp_id, Manager_id, depth, path, cycle) AS (
SELECT u.Emp_id, u.Manager_id, 1,
ARRAY[u.Emp_id],
false
FROM emp_manager u  
UNION ALL
SELECT u.Emp_id, u.Manager_id, cm.depth + 1,
(path || u.Emp_id)::character varying(32)[],
u.Emp_id = ANY(path)
FROM emp_manager u, circular_managers cm
WHERE u.Emp_id = cm.Manager_id AND NOT cycle
)
select
distinct (path) d
FROM circular_managers
WHERE cycle
AND path[1] = path[array_upper(path, 1)]) cm

但是，问题是，它正在返回层次结构的所有组合：

{28397468N,88518119N,87606705N,28397468N}
{87606705N,28397468N,88518119N,87606705N}
{88518119N,87606705N,28397468N,88518119N}
I need a simple answer like this:
28397468N>88518119N>87606705N>28397468N
even this will do:
28397468N>88518119N>87606705N

请帮忙！

Answer 1

所以所有引用：

{28397468N,88518119N,87606705N,28397468N}
{87606705N,28397468N,88518119N,87606705N}
{88518119N,87606705N,28397468N,88518119N}

是正确的，但只是从不同的元素开始。

我需要这样一个简单的答案：28397468N>88518119N>87606705N>28397468N

所以需要的是一个用于相同圆引用的过滤器。

让我们用一种方式来做：

• 对数组中不同的项进行排序
• 将它们聚合回来-因此对于所有引用，它将是"｛28397468N、87606705N、88518119N｝">
• 使用DISTINCT FIRST_value的生成值

WITH D (circle_ref ) AS (
VALUES
('{28397468N,88518119N,87606705N,28397468N}'::text[]),
('{87606705N,28397468N,88518119N,87606705N}'::text[]),
('{88518119N,87606705N,28397468N,88518119N}'::text[])
), ordered AS (
SELECT 
D.circle_ref,
(SELECT ARRAY_AGG(DISTINCT el ORDER BY el) FROM  UNNEST(D.circle_ref) AS el ) AS ordered_circle
FROM 
D
)
SELECT DISTINCT
FIRST_VALUE (circle_ref) OVER (PARTITION BY ordered_circle ORDER BY circle_ref) AS circle_ref 
FROM 
ordered;

circle_ref
{28397468N，88518119N，87606705N，28397468N}

为了防止循环引用，您可以使用闭包表和触发器-如https://stackoverflow.com/a/38705119/5962802中所述闭包表还可以让你轻松地找到某个主管的所有下属(无论层级有多深(，或者某个员工的所有直接老板(直到根(。

在使用rebuild_tree存储过程之前，必须从层次结构中删除所有循环引用。