Postgres 14

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

CREATE TABLE t(id serial primary key, owner varchar, amount real, date timestamp without time zone);
INSERT INTO t (amount, date)
SELECT random()*40 - 20 -- random values between -20 and +20
     , now() + interval '100000 second' * random()
  FROM generate_series(1,100) AS a
100 rows affected

SELECT t.*, sum(t.amount) FILTER (WHERE t.owner IS NULL) OVER (ORDER BY t.date DESC) - t.amount
  FROM t
 ORDER BY t.date ASC ;


id
owner
amount
date
?column?


36
null
10.780501
2022-01-13 22:02:15.641014
-222.56314

53
null
-14.020795
2022-01-13 22:08:45.040203
-208.54234

7
null
-3.557115
2022-01-13 22:13:30.748466
-204.98523

62
null
17.298552
2022-01-13 22:20:18.404332
-222.28378

71
null
5.0679445
2022-01-13 22:35:31.010431
-227.35173

65
null
-0.27956763
2022-01-13 22:51:33.863126
-227.07216

52
null
-8.860106
2022-01-13 23:28:46.041951
-218.21205

17
null
-10.049808
2022-01-13 23:29:38.485721
-208.16225

75
null
6.294199
2022-01-13 23:44:04.428828
-214.45645

69
null
12.4715805
2022-01-14 00:11:38.728527
-226.92804

57
null
5.7889934
2022-01-14 00:23:10.508533
-232.71701

70
null
-6.5824285
2022-01-14 00:24:22.013975
-226.13458

61
null
3.7773938
2022-01-14 00:26:43.934394
-229.91197

30
null
-10.700467
2022-01-14 00:27:47.358241
-219.2115

3
null
-11.690339
2022-01-14 00:30:48.267384
-207.52116

64
null
10.623133
2022-01-14 01:48:10.02097
-218.1443

76
null
7.731982
2022-01-14 01:51:18.096204
-225.87628

40
null
-9.534117
2022-01-14 02:24:57.248682
-216.34216

94
null
-5.2267165
2022-01-14 03:11:46.57769
-211.11545

80
null
-19.232159
2022-01-14 03:27:09.993899
-191.88329

31
null
-17.52829
2022-01-14 03:36:24.048332
-174.355

48
null
17.523487
2022-01-14 04:00:13.088683
-191.87848

67
null
-3.7616837
2022-01-14 04:04:46.953253
-188.11679

27
null
-16.611303
2022-01-14 04:13:20.757613
-171.5055

60
null
18.327263
2022-01-14 04:16:21.786192
-189.83276

16
null
19.883347
2022-01-14 05:22:57.80131
-209.71611

63
null
-8.154775
2022-01-14 05:29:13.370497
-201.56134

33
null
14.268306
2022-01-14 05:37:57.767443
-215.82965

81
null
-9.155749
2022-01-14 05:41:34.907108
-206.6739

54
null
-9.185932
2022-01-14 05:44:11.967095
-197.48798

55
null
2.2758896
2022-01-14 05:48:58.890262
-199.76387

39
null
-16.939337
2022-01-14 05:59:42.211911
-182.82454

15
null
-5.2916684
2022-01-14 06:05:52.039165
-177.53287

22
null
-7.4480233
2022-01-14 06:15:10.655885
-170.08484

86
null
10.921278
2022-01-14 06:37:43.02781
-181.00612

93
null
-1.571896
2022-01-14 06:50:06.402785
-179.43422

28
null
-4.0370035
2022-01-14 06:55:03.457773
-175.39722

95
null
-9.119241
2022-01-14 07:07:26.057098
-166.27797

35
null
-19.935028
2022-01-14 07:20:26.191831
-146.34294

47
null
-0.8029387
2022-01-14 07:52:53.688866
-145.54001

21
null
-1.1808699
2022-01-14 07:55:00.550533
-144.35915

99
null
2.9209774
2022-01-14 08:16:42.696839
-147.28012

2
null
-16.527342
2022-01-14 08:33:46.41855
-130.75278

24
null
0.035972282
2022-01-14 08:36:38.010173
-130.78874

83
null
-16.72047
2022-01-14 08:55:28.45384
-114.06827

98
null
13.457077
2022-01-14 09:30:17.199063
-127.525345

32
null
-16.52145
2022-01-14 09:32:07.783115
-111.00389

37
null
19.008507
2022-01-14 09:44:07.497087
-130.0124

49
null
4.535484
2022-01-14 09:46:53.977213
-134.54788

4
null
4.3449697
2022-01-14 10:00:08.537695
-138.89285

72
null
-4.1517415
2022-01-14 10:30:17.171921
-134.7411

23
null
-12.985805
2022-01-14 10:48:16.108584
-121.7553

66
null
-16.940275
2022-01-14 10:51:28.108113
-104.81502

20
null
-7.733367
2022-01-14 11:08:03.750079
-97.08165

10
null
5.490399
2022-01-14 11:19:22.356725
-102.57205

1
null
9.487829
2022-01-14 11:20:59.585562
-112.05988

41
null
-14.175132
2022-01-14 11:43:21.465035
-97.88475

45
null
-19.43417
2022-01-14 12:07:00.728766
-78.45058

56
null
3.9103422
2022-01-14 12:30:18.841687
-82.36092

38
null
1.6989182
2022-01-14 12:57:22.021652
-84.059845

100
null
-9.248399
2022-01-14 13:03:09.268946
-74.81145

84
null
-4.762145
2022-01-14 13:07:14.933442
-70.0493

14
null
-16.646078
2022-01-14 13:29:03.663109
-53.40322

77
null
-18.789686
2022-01-14 13:38:00.152643
-34.613533

18
null
0.32295376
2022-01-14 14:00:01.549117
-34.93649

51
null
-13.348128
2022-01-14 14:16:25.39216
-21.58836

79
null
-13.872721
2022-01-14 14:27:20.686859
-7.715639

85
null
-2.3170137
2022-01-14 14:34:40.778379
-5.3986263

34
null
14.282221
2022-01-14 15:17:31.818817
-19.680847

50
null
-10.495785
2022-01-14 15:28:27.990277
-9.185062

42
null
10.555904
2022-01-14 15:29:33.863988
-19.740967

91
null
-4.2768545
2022-01-14 15:46:50.994433
-15.464112

78
null
16.985819
2022-01-14 15:48:02.652572
-32.449932

58
null
-15.053669
2022-01-14 15:58:11.13597
-17.396263

29
null
-15.80177
2022-01-14 16:07:30.762774
-1.5944929

74
null
7.788827
2022-01-14 16:17:39.613059
-9.383321

97
null
5.0636835
2022-01-14 16:37:18.982147
-14.447004

44
null
-3.700091
2022-01-14 16:47:20.486502
-10.746914

11
null
-14.453311
2022-01-14 17:01:23.822001
3.706398

19
null
-11.390152
2022-01-14 17:06:51.074874
15.09655

5
null
-6.400852
2022-01-14 17:30:44.006086
21.497402

6
null
4.120532
2022-01-14 17:53:25.276753
17.37687

59
null
-4.286132
2022-01-14 18:09:59.308903
21.663002

25
null
-17.101961
2022-01-14 18:11:05.941535
38.76496

46
null
18.477106
2022-01-14 18:15:23.100871
20.287855

43
null
-16.311203
2022-01-14 18:33:15.754504
36.59906

96
null
-2.0384703
2022-01-14 20:08:04.400528
38.63753

90
null
-1.6835601
2022-01-14 20:19:19.021523
40.32109

87
null
3.232468
2022-01-14 20:22:04.553296
37.088623

13
null
15.501631
2022-01-14 20:43:13.633739
21.586992

9
null
-1.4007689
2022-01-14 21:43:30.911959
22.987762

26
null
-17.19957
2022-01-14 22:07:27.225393
40.187332

73
null
10.800302
2022-01-14 22:27:02.425198
29.387032

12
null
18.11673
2022-01-14 23:20:13.874104
11.270302

88
null
9.625981
2022-01-14 23:35:15.651542
1.6443205

89
null
-4.400582
2022-01-14 23:49:09.990022
6.044902

8
null
-8.790906
2022-01-14 23:58:54.967194
14.835808

68
null
13.911798
2022-01-15 00:27:28.57622
0.9240103

82
null
11.646772
2022-01-15 00:33:35.882721
-10.722762

92
null
-10.722762
2022-01-15 01:19:54.406274
0


CREATE OR REPLACE FUNCTION owner_update(OUT id integer, OUT owner varchar, OUT amount real, OUT date timestamp without time zone, OUT var real) RETURNS setof record LANGUAGE plpgsql AS
$$
BEGIN

LOOP

WITH list AS
( SELECT sum(t.amount) OVER(ORDER BY t.date DESC) AS running_amount , t.*
    FROM t
   WHERE t.owner IS NULL
)
UPDATE t
SET owner = 'XXX'
FROM list AS l
WHERE t.id = l.id
  AND l.running_amount - l.amount < 12 ;

IF NOT FOUND 
THEN EXIT ;
END IF ;

END LOOP ;

RETURN QUERY
SELECT t.*, sum(t.amount) FILTER (WHERE t.owner IS NULL) OVER (ORDER BY t.date DESC) - t.amount
  FROM t
 ORDER BY t.date ASC ;

END ;
$$ ;

SELECT * FROM owner_update() ;


id
owner
amount
date
var


36
XXX
10.780501
2022-01-13 22:02:15.641014
null

53
XXX
-14.020795
2022-01-13 22:08:45.040203
null

7
XXX
-3.557115
2022-01-13 22:13:30.748466
null

62
XXX
17.298552
2022-01-13 22:20:18.404332
null

71
XXX
5.0679445
2022-01-13 22:35:31.010431
null

65
XXX
-0.27956763
2022-01-13 22:51:33.863126
null

52
XXX
-8.860106
2022-01-13 23:28:46.041951
null

17
XXX
-10.049808
2022-01-13 23:29:38.485721
null

75
XXX
6.294199
2022-01-13 23:44:04.428828
null

69
XXX
12.4715805
2022-01-14 00:11:38.728527
null

57
XXX
5.7889934
2022-01-14 00:23:10.508533
null

70
XXX
-6.5824285
2022-01-14 00:24:22.013975
null

61
XXX
3.7773938
2022-01-14 00:26:43.934394
null

30
XXX
-10.700467
2022-01-14 00:27:47.358241
null

3
XXX
-11.690339
2022-01-14 00:30:48.267384
null

64
XXX
10.623133
2022-01-14 01:48:10.02097
null

76
XXX
7.731982
2022-01-14 01:51:18.096204
null

40
XXX
-9.534117
2022-01-14 02:24:57.248682
null

94
XXX
-5.2267165
2022-01-14 03:11:46.57769
null

80
XXX
-19.232159
2022-01-14 03:27:09.993899
null

31
XXX
-17.52829
2022-01-14 03:36:24.048332
null

48
XXX
17.523487
2022-01-14 04:00:13.088683
null

67
XXX
-3.7616837
2022-01-14 04:04:46.953253
null

27
XXX
-16.611303
2022-01-14 04:13:20.757613
null

60
XXX
18.327263
2022-01-14 04:16:21.786192
null

16
XXX
19.883347
2022-01-14 05:22:57.80131
null

63
XXX
-8.154775
2022-01-14 05:29:13.370497
null

33
XXX
14.268306
2022-01-14 05:37:57.767443
null

81
XXX
-9.155749
2022-01-14 05:41:34.907108
null

54
XXX
-9.185932
2022-01-14 05:44:11.967095
null

55
XXX
2.2758896
2022-01-14 05:48:58.890262
null

39
XXX
-16.939337
2022-01-14 05:59:42.211911
null

15
XXX
-5.2916684
2022-01-14 06:05:52.039165
null

22
XXX
-7.4480233
2022-01-14 06:15:10.655885
null

86
XXX
10.921278
2022-01-14 06:37:43.02781
null

93
XXX
-1.571896
2022-01-14 06:50:06.402785
null

28
XXX
-4.0370035
2022-01-14 06:55:03.457773
null

95
XXX
-9.119241
2022-01-14 07:07:26.057098
null

35
XXX
-19.935028
2022-01-14 07:20:26.191831
null

47
XXX
-0.8029387
2022-01-14 07:52:53.688866
null

21
XXX
-1.1808699
2022-01-14 07:55:00.550533
null

99
XXX
2.9209774
2022-01-14 08:16:42.696839
null

2
XXX
-16.527342
2022-01-14 08:33:46.41855
null

24
XXX
0.035972282
2022-01-14 08:36:38.010173
null

83
XXX
-16.72047
2022-01-14 08:55:28.45384
null

98
XXX
13.457077
2022-01-14 09:30:17.199063
null

32
XXX
-16.52145
2022-01-14 09:32:07.783115
null

37
XXX
19.008507
2022-01-14 09:44:07.497087
null

49
XXX
4.535484
2022-01-14 09:46:53.977213
null

4
XXX
4.3449697
2022-01-14 10:00:08.537695
null

72
XXX
-4.1517415
2022-01-14 10:30:17.171921
null

23
XXX
-12.985805
2022-01-14 10:48:16.108584
null

66
XXX
-16.940275
2022-01-14 10:51:28.108113
null

20
XXX
-7.733367
2022-01-14 11:08:03.750079
null

10
XXX
5.490399
2022-01-14 11:19:22.356725
null

1
XXX
9.487829
2022-01-14 11:20:59.585562
null

41
XXX
-14.175132
2022-01-14 11:43:21.465035
null

45
XXX
-19.43417
2022-01-14 12:07:00.728766
null

56
XXX
3.9103422
2022-01-14 12:30:18.841687
null

38
XXX
1.6989182
2022-01-14 12:57:22.021652
null

100
XXX
-9.248399
2022-01-14 13:03:09.268946
null

84
XXX
-4.762145
2022-01-14 13:07:14.933442
null

14
XXX
-16.646078
2022-01-14 13:29:03.663109
null

77
XXX
-18.789686
2022-01-14 13:38:00.152643
null

18
XXX
0.32295376
2022-01-14 14:00:01.549117
null

51
XXX
-13.348128
2022-01-14 14:16:25.39216
null

79
XXX
-13.872721
2022-01-14 14:27:20.686859
null

85
XXX
-2.3170137
2022-01-14 14:34:40.778379
null

34
XXX
14.282221
2022-01-14 15:17:31.818817
null

50
XXX
-10.495785
2022-01-14 15:28:27.990277
null

42
XXX
10.555904
2022-01-14 15:29:33.863988
null

91
XXX
-4.2768545
2022-01-14 15:46:50.994433
null

78
XXX
16.985819
2022-01-14 15:48:02.652572
null

58
XXX
-15.053669
2022-01-14 15:58:11.13597
null

29
XXX
-15.80177
2022-01-14 16:07:30.762774
null

74
XXX
7.788827
2022-01-14 16:17:39.613059
null

97
XXX
5.0636835
2022-01-14 16:37:18.982147
null

44
XXX
-3.700091
2022-01-14 16:47:20.486502
null

11
XXX
-14.453311
2022-01-14 17:01:23.822001
null

19
XXX
-11.390152
2022-01-14 17:06:51.074874
null

5
XXX
-6.400852
2022-01-14 17:30:44.006086
null

6
XXX
4.120532
2022-01-14 17:53:25.276753
null

59
XXX
-4.286132
2022-01-14 18:09:59.308903
null

25
XXX
-17.101961
2022-01-14 18:11:05.941535
null

46
XXX
18.477106
2022-01-14 18:15:23.100871
null

43
XXX
-16.311203
2022-01-14 18:33:15.754504
null

96
XXX
-2.0384703
2022-01-14 20:08:04.400528
null

90
XXX
-1.6835601
2022-01-14 20:19:19.021523
null

87
XXX
3.232468
2022-01-14 20:22:04.553296
null

13
XXX
15.501631
2022-01-14 20:43:13.633739
null

9
XXX
-1.4007689
2022-01-14 21:43:30.911959
null

26
XXX
-17.19957
2022-01-14 22:07:27.225393
null

73
XXX
10.800302
2022-01-14 22:27:02.425198
null

12
XXX
18.11673
2022-01-14 23:20:13.874104
null

88
XXX
9.625981
2022-01-14 23:35:15.651542
null

89
XXX
-4.400582
2022-01-14 23:49:09.990022
null

8
XXX
-8.790906
2022-01-14 23:58:54.967194
null

68
XXX
13.911798
2022-01-15 00:27:28.57622
null

82
XXX
11.646772
2022-01-15 00:33:35.882721
null

92
XXX
-10.722762
2022-01-15 01:19:54.406274
null

id	owner	amount	date	?column?
36	null	10.780501	2022-01-13 22:02:15.641014	-222.56314
53	null	-14.020795	2022-01-13 22:08:45.040203	-208.54234
7	null	-3.557115	2022-01-13 22:13:30.748466	-204.98523
62	null	17.298552	2022-01-13 22:20:18.404332	-222.28378
71	null	5.0679445	2022-01-13 22:35:31.010431	-227.35173
65	null	-0.27956763	2022-01-13 22:51:33.863126	-227.07216
52	null	-8.860106	2022-01-13 23:28:46.041951	-218.21205
17	null	-10.049808	2022-01-13 23:29:38.485721	-208.16225
75	null	6.294199	2022-01-13 23:44:04.428828	-214.45645
69	null	12.4715805	2022-01-14 00:11:38.728527	-226.92804
57	null	5.7889934	2022-01-14 00:23:10.508533	-232.71701
70	null	-6.5824285	2022-01-14 00:24:22.013975	-226.13458
61	null	3.7773938	2022-01-14 00:26:43.934394	-229.91197
30	null	-10.700467	2022-01-14 00:27:47.358241	-219.2115
3	null	-11.690339	2022-01-14 00:30:48.267384	-207.52116
64	null	10.623133	2022-01-14 01:48:10.02097	-218.1443
76	null	7.731982	2022-01-14 01:51:18.096204	-225.87628
40	null	-9.534117	2022-01-14 02:24:57.248682	-216.34216
94	null	-5.2267165	2022-01-14 03:11:46.57769	-211.11545
80	null	-19.232159	2022-01-14 03:27:09.993899	-191.88329
31	null	-17.52829	2022-01-14 03:36:24.048332	-174.355
48	null	17.523487	2022-01-14 04:00:13.088683	-191.87848
67	null	-3.7616837	2022-01-14 04:04:46.953253	-188.11679
27	null	-16.611303	2022-01-14 04:13:20.757613	-171.5055
60	null	18.327263	2022-01-14 04:16:21.786192	-189.83276
16	null	19.883347	2022-01-14 05:22:57.80131	-209.71611
63	null	-8.154775	2022-01-14 05:29:13.370497	-201.56134
33	null	14.268306	2022-01-14 05:37:57.767443	-215.82965
81	null	-9.155749	2022-01-14 05:41:34.907108	-206.6739
54	null	-9.185932	2022-01-14 05:44:11.967095	-197.48798
55	null	2.2758896	2022-01-14 05:48:58.890262	-199.76387
39	null	-16.939337	2022-01-14 05:59:42.211911	-182.82454
15	null	-5.2916684	2022-01-14 06:05:52.039165	-177.53287
22	null	-7.4480233	2022-01-14 06:15:10.655885	-170.08484
86	null	10.921278	2022-01-14 06:37:43.02781	-181.00612
93	null	-1.571896	2022-01-14 06:50:06.402785	-179.43422
28	null	-4.0370035	2022-01-14 06:55:03.457773	-175.39722
95	null	-9.119241	2022-01-14 07:07:26.057098	-166.27797
35	null	-19.935028	2022-01-14 07:20:26.191831	-146.34294
47	null	-0.8029387	2022-01-14 07:52:53.688866	-145.54001
21	null	-1.1808699	2022-01-14 07:55:00.550533	-144.35915
99	null	2.9209774	2022-01-14 08:16:42.696839	-147.28012
2	null	-16.527342	2022-01-14 08:33:46.41855	-130.75278
24	null	0.035972282	2022-01-14 08:36:38.010173	-130.78874
83	null	-16.72047	2022-01-14 08:55:28.45384	-114.06827
98	null	13.457077	2022-01-14 09:30:17.199063	-127.525345
32	null	-16.52145	2022-01-14 09:32:07.783115	-111.00389
37	null	19.008507	2022-01-14 09:44:07.497087	-130.0124
49	null	4.535484	2022-01-14 09:46:53.977213	-134.54788
4	null	4.3449697	2022-01-14 10:00:08.537695	-138.89285
72	null	-4.1517415	2022-01-14 10:30:17.171921	-134.7411
23	null	-12.985805	2022-01-14 10:48:16.108584	-121.7553
66	null	-16.940275	2022-01-14 10:51:28.108113	-104.81502
20	null	-7.733367	2022-01-14 11:08:03.750079	-97.08165
10	null	5.490399	2022-01-14 11:19:22.356725	-102.57205
1	null	9.487829	2022-01-14 11:20:59.585562	-112.05988
41	null	-14.175132	2022-01-14 11:43:21.465035	-97.88475
45	null	-19.43417	2022-01-14 12:07:00.728766	-78.45058
56	null	3.9103422	2022-01-14 12:30:18.841687	-82.36092
38	null	1.6989182	2022-01-14 12:57:22.021652	-84.059845
100	null	-9.248399	2022-01-14 13:03:09.268946	-74.81145
84	null	-4.762145	2022-01-14 13:07:14.933442	-70.0493
14	null	-16.646078	2022-01-14 13:29:03.663109	-53.40322
77	null	-18.789686	2022-01-14 13:38:00.152643	-34.613533
18	null	0.32295376	2022-01-14 14:00:01.549117	-34.93649
51	null	-13.348128	2022-01-14 14:16:25.39216	-21.58836
79	null	-13.872721	2022-01-14 14:27:20.686859	-7.715639
85	null	-2.3170137	2022-01-14 14:34:40.778379	-5.3986263
34	null	14.282221	2022-01-14 15:17:31.818817	-19.680847
50	null	-10.495785	2022-01-14 15:28:27.990277	-9.185062
42	null	10.555904	2022-01-14 15:29:33.863988	-19.740967
91	null	-4.2768545	2022-01-14 15:46:50.994433	-15.464112
78	null	16.985819	2022-01-14 15:48:02.652572	-32.449932
58	null	-15.053669	2022-01-14 15:58:11.13597	-17.396263
29	null	-15.80177	2022-01-14 16:07:30.762774	-1.5944929
74	null	7.788827	2022-01-14 16:17:39.613059	-9.383321
97	null	5.0636835	2022-01-14 16:37:18.982147	-14.447004
44	null	-3.700091	2022-01-14 16:47:20.486502	-10.746914
11	null	-14.453311	2022-01-14 17:01:23.822001	3.706398
19	null	-11.390152	2022-01-14 17:06:51.074874	15.09655
5	null	-6.400852	2022-01-14 17:30:44.006086	21.497402
6	null	4.120532	2022-01-14 17:53:25.276753	17.37687
59	null	-4.286132	2022-01-14 18:09:59.308903	21.663002
25	null	-17.101961	2022-01-14 18:11:05.941535	38.76496
46	null	18.477106	2022-01-14 18:15:23.100871	20.287855
43	null	-16.311203	2022-01-14 18:33:15.754504	36.59906
96	null	-2.0384703	2022-01-14 20:08:04.400528	38.63753
90	null	-1.6835601	2022-01-14 20:19:19.021523	40.32109
87	null	3.232468	2022-01-14 20:22:04.553296	37.088623
13	null	15.501631	2022-01-14 20:43:13.633739	21.586992
9	null	-1.4007689	2022-01-14 21:43:30.911959	22.987762
26	null	-17.19957	2022-01-14 22:07:27.225393	40.187332
73	null	10.800302	2022-01-14 22:27:02.425198	29.387032
12	null	18.11673	2022-01-14 23:20:13.874104	11.270302
88	null	9.625981	2022-01-14 23:35:15.651542	1.6443205
89	null	-4.400582	2022-01-14 23:49:09.990022	6.044902
8	null	-8.790906	2022-01-14 23:58:54.967194	14.835808
68	null	13.911798	2022-01-15 00:27:28.57622	0.9240103
82	null	11.646772	2022-01-15 00:33:35.882721	-10.722762
92	null	-10.722762	2022-01-15 01:19:54.406274	0