Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id int, measurement numeric);
INSERT INTO tbl VALUES 
  (1,  1.5)
, (2,  1.4)
, (3,  1.8)
, (4,  2.6)
, (5,  3.7)
, (6,  3.5)
, (7,  3.0)
, (8,  2.6)
, (9,  2.5)
, (10, 2.8);

CREATE TABLE



INSERT 0 10


-- rCTE
WITH RECURSIVE rcte AS (
   (
   SELECT id
        , measurement
        , measurement - 1 AS grp_min
        , measurement + 1 AS grp_max
        , 1 AS grp
   FROM   tbl
   ORDER  BY id
   LIMIT  1
   )

   UNION ALL
   (
   SELECT t.id
        , t.measurement
        , CASE WHEN t.same_grp THEN r.grp_min ELSE t.measurement - 1 END  -- AS grp_min 
        , CASE WHEN t.same_grp THEN r.grp_max ELSE t.measurement + 1 END  -- AS grp_max
        , CASE WHEN t.same_grp THEN r.grp     ELSE r.grp + 1         END  -- AS grp
   FROM   rcte r 
   CROSS  JOIN LATERAL (
      SELECT *, t.measurement BETWEEN r.grp_min AND r.grp_max AS same_grp
      FROM   tbl t
      WHERE  t.id > r.id
      ORDER  BY t.id
      LIMIT  1
      ) t
   )
   )
SELECT id, measurement, grp
FROM   rcte;


id
measurement
grp


1
1.5
1

2
1.4
1

3
1.8
1

4
2.6
2

5
3.7
3

6
3.5
3

7
3.0
3

8
2.6
4

9
2.5
4

10
2.8
4



SELECT 10


-- plpgsql function
CREATE OR REPLACE FUNCTION f_measurement_groups(_threshold numeric = 1)
  RETURNS TABLE (id int, grp int, measurement numeric) AS
$func$
DECLARE
   _grp_min numeric;
   _grp_max numeric;
BEGIN
   grp := 0;  -- init

   FOR id, measurement IN
      SELECT * FROM tbl t ORDER BY t.id
   LOOP
      IF measurement BETWEEN _grp_min AND _grp_max THEN
         RETURN NEXT;
      ELSE
         SELECT INTO grp    , _grp_min                , _grp_max
                     grp + 1, measurement - _threshold, measurement + _threshold;
         RETURN NEXT;
      END IF;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

CREATE FUNCTION


-- call:
SELECT * FROM f_measurement_groups();


id
grp
measurement


1
1
1.5

2
1
1.4

3
1
1.8

4
2
2.6

5
3
3.7

6
3
3.5

7
3
3.0

8
4
2.6

9
4
2.5

10
4
2.8



SELECT 10