Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE assets (
  id serial PRIMARY KEY
, name text
, owner text
);

CREATE TABLE users (
  id serial  PRIMARY KEY
, username text
, ref1 text
, ref2 text
);

INSERT INTO assets (name, OWNER) VALUES
  ('#1', 'a')
, ('#2', 'b')
, ('#3', 'c')
, ('#4', 'a')
, ('#5', 'c')
, ('#6', 'd')
, ('#7', 'e')
, ('#8', 'd')
, ('#9', 'a')
, ('#10', 'a')
, ('#11', 'z')
;

INSERT INTO users (username, ref1, ref2) VALUES
  ('bobo', 'a', 'd')
, ('toto', 'b', 'e')
, ('momo', 'c', 'd')
, ('lolo', 'a', 'f')
, ('popo', 'c', 'f')
;

-- For result comparison
-- drop table results;
CREATE temp TABLE results (
  ids int[]
, usernames text[]
, refs1 text[]
, refs2 text[]
, asset_ids int[]
, asset_count int
);

INSERT INTO results (ids, usernames, refs1, refs2, asset_ids, asset_count) VALUES
  (array[1,3,4,5], array['bobo', 'momo', 'lolo', 'popo'], array['a', 'c'], array['d', 'f'], array[1,3,4,5,6,8], 6),
  (array[2], array['toto'], array['b'], array['e'], array[2,7], 2);

TABLE results;

CREATE TABLE



CREATE TABLE



INSERT 0 11



INSERT 0 5



CREATE TABLE



INSERT 0 2




ids
usernames
refs1
refs2
asset_ids
asset_count


{1,3,4,5}
{bobo,momo,lolo,popo}
{a,c}
{d,f}
{1,3,4,5,6,8}
6

{2}
{toto}
{b}
{e}
{2,7}
2



SELECT 2


SHOW temp_buffers;


temp_buffers


8MB



SHOW


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 the assumption 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
   (
   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$;

CREATE FUNCTION


SELECT * FROM f_merged_users();


ids
usernames
refs1
refs2
asset_ids
asset_count


{1,3,4,5}
{bobo,momo,lolo,popo}
{a,c}
{d,f}
{1,3,4,5,6,8,9,10}
8

{2}
{toto}
{b}
{e}
{2,7}
2

null
null
null
{z}
{11}
1



SELECT 3
ids	usernames	refs1	refs2	asset_ids	asset_count
{1,3,4,5}	{bobo,momo,lolo,popo}	{a,c}	{d,f}	{1,3,4,5,6,8}	6
{2}	{toto}	{b}	{e}	{2,7}	2