SELECT * FROM assets;

SELECT * FROM users;

SELECT * FROM results;

SELECT
  ARRAY_AGG(DISTINCT u.id) AS ids,
  ARRAY_AGG(DISTINCT u.username) AS usernames,
  ARRAY_AGG(DISTINCT u.ref1) AS refs1,
  ARRAY_AGG(DISTINCT u.ref2) AS refs2,
  COUNT(DISTINCT a.id) AS asset_count
FROM assets a
JOIN users u ON a.owner = u.ref1 OR a.owner = u.ref2
GROUP BY a.owner
ORDER BY MIN(a.id);

with recursive 
    nodes as (
        select u.id, r.ref
        from users u 
        cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
    ),
    edges as (
        select distinct n1.id as id1, n2.id as id2
        from nodes n1 
        inner join nodes n2 on n1.ref = n2.ref
    ),
    rcte as (
        select id1, id2, array[id1] as visited from edges where id1 = id2
        union all
        select r.id1, e.id2, r.visited || e.id2
        from rcte r
        inner join edges e on e.id1 = r.id2
        where e.id2 <> all(r.visited) 
    ),
    groups as (
        select id1 as id, array_agg(distinct id2 order by id2) as ids
        from rcte
        group by id1
    )
select * from groups order by id

with recursive 
    nodes  as (...),
    edges  as (...),
    rcte   as (...),
    groups as (...)
select g.ids,
    array_agg(distinct u.username) as usernames,
    array_agg(distinct u.ref1) as refs1,
    case when g.ids is null then array_agg(distinct a.owner) else array_agg(distinct u.ref2) end as refs2,
    array_agg(distinct a.id) as asset_ids,
    count(distinct a.id) as asset_count
from assets a
left join users u on a.owner in (u.ref1, u.ref2)
left join groups g on g.id = u.id
group by g.ids

with recursive 
    nodes as (
        select u.id, r.ref
        from users u 
        cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
    ),
    edges as (
        select distinct n1.id as id1, n2.id as id2
        from nodes n1 
        inner join nodes n2 on n1.ref = n2.ref
    ),
    rcte as (
        select id1 as id, array[id1] as visited from edges where id1 = id2
        union all
        select id, array_agg(distinct visited) as visited
        from (
            select r.id, v.visited
            from rcte r
            inner join edges e on e.id1 = any(r.visited)
            cross join lateral unnest(r.visited || e.id2) v(visited)
            where e.id2 <> all(r.visited)
        ) as x
        group by id
    ),
    groups as (
        select distinct on (id) id, visited as ids 
        from rcte 
        order by id, array_length(visited, 1) desc 
    )
select * from groups g order by id

CREATE OR REPLACE FUNCTION f_merged_users()
  RETURNS TABLE (
     ids int[]             -- adapt to your actual data types !!!
   , usernames text[]
   , refs1 text[]
   , refs2 text[]
   , asset_ids int[]
   , asset_count int
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   /*
   -- set enough temp buffers, so temp tables don't spill to disk (optional)
   -- NOTE: this can't be used after temporary tables have been accessed in the session
   -- using 2x size of users table. adapt to your needs!
   -- only fires when the new size is bigger, and it has not been set manually, yet
   PERFORM set_config('temp_buffers', (pg_table_size('users') * 2 / 8096)::text, true)
   FROM   pg_settings s
   WHERE  s.name = 'temp_buffers'
   AND    pg_table_size('users') * 2 / 8096 > s.setting::bigint
   AND    s.source = 'default';
   */
   -- create two temp tables: one for ref1, one for ref2
   -- based on your information that ref1 & ref2 don't overlap
   CREATE TEMP TABLE r1 ON COMMIT DROP AS
   SELECT ARRAY[ref1] AS r, array_agg(id) AS i
   FROM  (TABLE users ORDER BY id) t
   GROUP  BY ref1
   ORDER  BY ref1;
   CREATE TEMP TABLE r2 ON COMMIT DROP AS
   SELECT ARRAY[ref2] AS r, array_agg(id) AS i
   FROM  (TABLE users ORDER BY id) t
   GROUP  BY ref2
   ORDER  BY ref2;
   -- fold rows where a common link in the other table exists
   -- achieve that by deleting rows and inserting the merged results
   -- loop back and forth until no more rows can be folded
   LOOP
      WITH d AS (
         DELETE FROM r2
         USING  r1
         WHERE  EXISTS (
            SELECT FROM r2 r0
            WHERE  r1.i && r2.i
            AND    r1.i && r0.i
            AND    r2.ctid <> r0.ctid
            )
         RETURNING r1.ctid, r2.*
         )
      INSERT INTO r2
      SELECT r.r, i.i
      FROM  (
         SELECT ctid, array_agg(ref ORDER BY ref) AS r
         FROM   d, unnest(d.r) ref
         GROUP  BY 1
         ) r
      JOIN  (
         SELECT ctid, array_agg(id ORDER BY id) AS i
         FROM   d, unnest(d.i) id
         GROUP  BY 1
         )  i USING (ctid);
      EXIT WHEN NOT FOUND;  -- no folding happened, stop loop
      WITH d AS (
         DELETE FROM r1
         USING  r2
         WHERE  EXISTS (
            SELECT FROM r1 r0
            WHERE  r2.i && r1.i
            AND    r2.i && r0.i
            AND    r1.ctid <> r0.ctid
            )
         RETURNING r2.ctid, r1.*
         )
      INSERT INTO r1
      SELECT r.r, i.i
      FROM  (
         SELECT ctid, array_agg(ref ORDER BY ref) AS r
         FROM   d, unnest(d.r) ref
         GROUP  BY 1
         ) r
      JOIN  (
         SELECT ctid, array_agg(id ORDER BY id) AS i
         FROM   d, unnest(d.i) id
         GROUP  BY 1
         ) i USING (ctid);
         
      EXIT WHEN NOT FOUND;     -- no folding happened, stop loop
   END LOOP;
   -- output result
   RETURN QUERY
   (
   SELECT i                         -- AS ids
        , u.usernames
        , r1.r                      -- AS refs1
        , r2.r                      -- AS refs2
        , a.asset_ids
        , cardinality(a.asset_ids)  -- AS asset_count
   FROM   r1
   JOIN   r2 USING (i)
   CROSS  JOIN LATERAL (
      SELECT ARRAY (
         SELECT username
         FROM   users u
         WHERE  u.id = ANY (i)
         ORDER  BY u.id
         ) AS usernames
      ) u
   CROSS  JOIN LATERAL (
      SELECT ARRAY (
         SELECT a.id
         FROM   assets a
         WHERE  a.owner = ANY (r1.r || r2.r)
         ORDER  BY a.id
         ) AS asset_ids
      ) a
   ORDER  BY i
   )
   UNION ALL  --  add "missing entries in the user table"
   (
   SELECT null             -- AS ids
        , null             -- AS usernames
        , null             -- AS refs1
        , ARRAY[a.owner]   -- AS refs2
        , ARRAY[a.id]      -- AS ids
        , 1                -- AS asset_count
   FROM   assets a
   WHERE  NOT EXISTS (
      SELECT FROM users u
      WHERE  a.owner IN (u.ref1, u.ref2)
      )
   ORDER  BY a.id
   );
END
$func$;

SELECT * FROM f_merged_users();

select chr(component::int)       as "the_group",
       array_agg(chr(node::int)) as "ids"
from pgr_connectedComponents(
  'SELECT id, 
          ascii(ref1) as source, 
          ascii(ref2) as target, 
          1 as cost, 
          1 as reverse_cost 
   FROM users') 
group by component;
-- the_group |    ids
-------------+-----------
-- a         | {a,c,d,f}
-- b         | {b,e}