sql - 在 PostgreSQL 表中查找循环引用？

Question

我有一个包含属性(ID int、SourceID int、TargetID int、TargetType int)的表

ID SourceID TargetID---------------------1   123       456  2   456       789  3   1         123  4   456        1   5   2          1   

我想找出所有循环引用。我想为此编写 PL/pgsql 函数。

此处为 ID 4 的循环引用 = 456 1 123 456

我想找到这样的实例。谁能建议我如何进行。

Answer 1

这类似于 How to prevent a self-referencing table from becoming circular (SQL-Server 版本)，它使用员工/经理表的教学示例提出问题。

除了检测循环引用的功能之外，我还对有一个触发器来防止在数据库中插入或更新此类数据很感兴趣。

这里是我改编自mellamokb的代码PLpgSQL/PostgreSQL的回答:

-- example of table structure
CREATE TABLE employee (
   ID INTEGER NOT NULL,
   ManagerID INTEGER,
   CONSTRAINT a PRIMARY KEY (ID),
   CONSTRAINT b FOREIGN KEY (ManagerID) REFERENCES employee (ID))

-- function to be used inside trigger
CREATE OR REPLACE FUNCTION CircularReference()
RETURNS TRIGGER
AS
$$ 
DECLARE _CircularReferenceExists BIT(1) := 0;

BEGIN  

    WITH RECURSIVE cte AS (
        SELECT E.* FROM employee E WHERE E.ID = new.ManagerID
        UNION -- union instead of union all to prevent infinite looping.
        SELECT E.* FROM employee E JOIN cte C ON C.ManagerID = E.ID AND E.ID <> new.ManagerID
    )
    SELECT count(*) INTO _CircularReferenceExists FROM cte C WHERE C.ManagerID = new.ManagerID;

    IF (SELECT _CircularReferenceExists <> B'0')
    THEN
    RAISE EXCEPTION 'Circular Reference found: ManagerID = %', new.ManagerID;
    END IF;

RETURN NEW;       
END
$$ LANGUAGE plpgsql;

-- trigger
CREATE TRIGGER CircularReference
AFTER INSERT OR UPDATE
ON employee
FOR EACH ROW
EXECUTE PROCEDURE CircularReference();

如果数据库中已有数据，只需创建触发器并使用 UPDATE 语句使其开始检测循环引用。

UPDATE employee SET ID = ID;