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;
DECLARE _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
)
SELECT
COALESCE(c.total, 0)
INTO
_previous_value
FROM c;
EXECUTE $dsql$ UPDATE table1
SET value = $1
WHERE table1.id = $2 ;
$dsql$ USING _previous_value, _r.id;
END LOOP;
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