Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table my_table(id integer primary key, related_id integer[]) ;

insert into my_table values
(712, '{1116}'),
(837, '{1116,1127}'),
(1116, '{712,837}'),
(1127, '{837}') ;
4 rows affected

WITH RECURSIVE 
 un AS (SELECT id,unnest(related_id) as parent FROM my_table),
 rec (id) as
 (
   SELECT id,parent,array[id] as ids from un
     UNION ALL
   SELECT rec.id,un.parent,ids || un.id FROM rec JOIN un ON rec.parent = un.id where un.id <> all(ids)
 ),
 j AS (SELECT distinct on (id) ids,id FROM rec order by id, cardinality(ids) desc)                                                           
 SELECT ops.*,j.ids as full_ids FROM my_table ops LEFT JOIN j ON ops.id=j.id;


id
related_id
full_ids


712
{1116}
{712,1116,837,1127}

837
{1116,1127}
{837,1116,712}

1116
{712,837}
{1116,837,1127}

1127
{837}
{1127,837,1116,712}


WITH RECURSIVE list (id, related_id, parent, full_ids) AS
(
SELECT id, related_id, related_id, id || related_id
  FROM my_table
UNION ALL
SELECT l.id, l.related_id, array_remove (l.parent, t.id) || t.related_id, l.full_ids || t.sub_id
  FROM list AS l
 INNER JOIN
     ( my_table AS m
       CROSS JOIN LATERAL unnest(m.related_id) AS sub_id
     ) AS t
    ON array[t.id] <@ l.parent
   AND NOT array[t.sub_id] <@ l.full_ids
)
SELECT DISTINCT ON (id) id, related_id, full_ids
  FROM list
 ORDER BY id, array_length(full_ids, 1) DESC


id
related_id
full_ids


712
{1116}
{712,1116,837,1127}

837
{1116,1127}
{837,1116,1127,712}

1116
{712,837}
{1116,712,837,1127}

1127
{837}
{1127,837,1116,712}

id	related_id	full_ids
712	{1116}	{712,1116,837,1127}
837	{1116,1127}	{837,1116,712}
1116	{712,837}	{1116,837,1127}
1127	{837}	{1127,837,1116,712}