Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE messages(id int, user_id int);
INSERT INTO messages(id, user_id) VALUES
  (1, 1)
, (2, 1)
, (3, 2)
, (4, 2)
, (5, 1)
;

CREATE TABLE



INSERT 0 5


SELECT grp, user_id, array_agg(id) AS ids
FROM  (
   SELECT id
        , user_id
        , row_number() OVER (ORDER BY id) -
          row_number() OVER (PARTITION BY user_id ORDER BY id) AS grp
   FROM   messages
   ORDER  BY 1
   ) t
GROUP  BY grp, user_id
ORDER  BY min(id);


grp
user_id
ids


0
1
{1,2}

2
2
{3,4}

2
1
{5}



SELECT 3


CREATE OR REPLACE FUNCTION f_msg_groups()
  RETURNS TABLE (ids int[])
  LANGUAGE plpgsql AS
$func$
DECLARE
   _id    int;
   _uid   int;
   _id0   int;                         -- id of last row
   _uid0  int;                         -- user_id of last row
BEGIN
   FOR _id, _uid IN
       SELECT id, user_id FROM messages ORDER BY id
   LOOP
       IF _uid <> _uid0 THEN
          RETURN QUERY VALUES (ids);   -- output row (never happens after 1 row)
          ids := ARRAY[_id];           -- start new array
       ELSE
          ids := ids || _id;           -- add to array
       END IF;

       _id0  := _id;
       _uid0 := _uid;                  -- remember last row
   END LOOP;

   RETURN QUERY VALUES (ids);          -- output last iteration
END
$func$;

CREATE FUNCTION


SELECT * FROM f_msg_groups();


ids


{1,2}

{3,4}

{5}



SELECT 3