PostgreSQL check circular reference for parent_id

check_circular_reference.sql

CREATE OR REPLACE FUNCTION check_circular_reference() RETURNS TRIGGER AS $$
DECLARE
    circular_found int;
BEGIN
    IF NEW.parent_id IS NOT NULL
    THEN
        IF NEW.parent_id = NEW.id
        THEN
            RAISE EXCEPTION 'circular reference detected';
        END IF;

        WITH RECURSIVE circular_reference AS (
            SELECT id, parent_id FROM my_table WHERE parent_id = NEW.id
            UNION
            SELECT t.id, t.parent_id
            FROM my_table t
            INNER JOIN circular_reference cr ON cr.id = t.parent_id
        )
        SELECT 1 INTO circular_found FROM circular_reference WHERE id = NEW.parent_id;

        IF FOUND THEN
            RAISE EXCEPTION 'circular reference detected';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS circular_reference_trigger ON my_table;
CREATE TRIGGER circular_reference_trigger BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE FUNCTION check_circular_reference();