By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table table1(id,date,quantity,"value")as values
(1,'2024-10-01',1,1)
,(2,'2024-10-02',1,1)
,(3,'2024-10-03',1,1)
,(4,'2024-10-04',1,1)
,(5,'2024-10-05',1,1)
,(6,'2024-10-06',1,1)
,(7,'2024-10-07',1,1)
;
SELECT 7
CREATE OR REPLACE FUNCTION functiontest()
RETURNS VOID LANGUAGE plpgsql AS $f$
DECLARE _r RECORD;
_previous_value DOUBLE PRECISION := 0;
BEGIN
FOR _r IN SELECT table1.date, table1.id
FROM table1
ORDER BY table1.date, table1.id
LOOP WITH c AS(
SELECT table1.id
, table1.quantity + _previous_value AS total
FROM table1
WHERE table1.id = _r.id
ORDER BY table1.date
, table1.id
LIMIT 1
),updated as(
UPDATE table1
SET value = COALESCE(c.total, 0)
FROM c
WHERE table1.id = c.id
RETURNING value)--value used in that update gets returned
SELECT value
FROM updated
INTO _previous_value;--and saved for the next iteration
END LOOP;
END $f$;
CREATE FUNCTION
begin;
select functiontest();
table table1;
rollback;
BEGIN
| functiontest |
|---|
SELECT 1
| id | date | quantity | value |
|---|---|---|---|
| 1 | 2024-10-01 | 1 | 1 |
| 2 | 2024-10-02 | 1 | 2 |
| 3 | 2024-10-03 | 1 | 3 |
| 4 | 2024-10-04 | 1 | 4 |
| 5 | 2024-10-05 | 1 | 5 |
| 6 | 2024-10-06 | 1 | 6 |
| 7 | 2024-10-07 | 1 | 7 |
SELECT 7
ROLLBACK