Find loop references in the PostgreSQL table?

create extension intarray; 
The first element of the int array arr is id. The rest of the array contains consecutive reference sources -> targets.
 
If the second and last elements of the array are equal, find Circular references. (1)
 
We must look for internal circular references and eliminate them (or we will finish stack overflow). (2)
 
 
create or replace function find_cref(arr int[])
returns setof int[] language plpgsql
as $$
declare
 vlen int = #arr;
 vtarget int;< br />begin
 if arr[2] = arr[vlen] then - (1)
 return query select arr;< br /> else
 if #uniq(sort(subarray(arr, 2)))+ 1 = vlen then - (2)
 for vtarget in
 select target from the_table where source = arr[vlen]
 loop
 return query select find_cref (arr+ vtarget);
 end loop;
 end if;
 end if;
end $$ ;

select c[1] id, subarray(c, 2) cref 
from (
 select find_cref(array[id, source, target]) c
 from the_table) x

I have an attribute table (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 

I want to find all circular references. I want to write a PL/pgsql function for this.

Here ID 4 = 456 1 123 456 circular reference

I want to find such an example. Can anyone suggest me how to do this.

This can be done by the following recursive function. This function uses intarray extension.

create extension intarray;
 
The first element of the int array arr is id. The rest of the array contains a continuous reference source -> target.
 
If the second and last elements of the array are equal, find Circular references. (1)
 
We must look for internal circular references and eliminate them (or we will finish stack overflow). (2)
 
 
create or replace function find_cref(arr int[])
returns setof int[] language plpgsql
as $$
declare
 vlen int = #arr;
 vtarget int;< br />begin
 if arr[2] = arr[vlen] then - (1)
 return query select arr;
 else
 if #uniq(sort(subarray( arr, 2)))+ 1 = vlen then - (2)
 for vtarget in
 select target from the_table where source = arr[vlen]
 loop
 return query select find_cref (arr+ vtarget);
 end loop;
 end if;
 end if;
end $$;

select c[1] id, subarray(c, 2) cref 
from (
 select find_cref(array[id, source, target]) c
 from the_table) x

< /p>