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 tbl (
symbol text NOT NULL
, date timestamptz
, close numeric
, computed numeric
, primary key (symbol, date)
);
INSERT INTO tbl VALUES
('TBBB', '2024-02-22 14:30:00+00', '19.05', NULL),
('TBBB', '2024-02-23 14:30:00+00', '19.55', NULL),
('TBBB', '2024-02-24 14:30:00+00', '20.6', NULL),
('TBBB', '2024-02-25 14:30:00+00', '21.3', NULL),
('TBBB', '2024-02-26 14:30:00+00', '20.43', '20.4'),
('TBBB', '2024-02-27 14:30:00+00', '20.21', NULL),
('TBBB', '2024-02-28 14:30:00+00', '20.74', NULL),
('TBBB', '2024-02-29 14:30:00+00', '20.09', NULL),
('TBBB', '2024-03-01 14:30:00+00', '20.79', NULL),
('TBBB', '2024-03-02 14:30:00+00', '20.87', NULL),
('TBBB', '2024-03-03 14:30:00+00', '20.69', NULL),
('TBBB', '2024-03-04 14:30:00+00', '20.19', NULL),
('TBBB', '2024-03-05 14:30:00+00', '20.9', '5'), -- another existing value!
('TBBB', '2024-03-06 14:30:00+00', '20.99', NULL),
('TBBB', '2024-03-07 14:30:00+00', '21.28', NULL),
('TBBB', '2024-03-08 14:30:00+00', '21.27', NULL);
CREATE TABLE
INSERT 0 16
-- create function
CREATE OR REPLACE FUNCTION f_compute()
RETURNS SETOF tbl
LANGUAGE plpgsql AS
$func$
DECLARE
r tbl; -- use table type as row variable
r0 tbl;
BEGIN
FOR r IN
SELECT * FROM tbl t ORDER BY t.symbol, t.date
LOOP
IF r.symbol = r0.symbol -- same symbol
AND r0.computed IS NOT NULL -- last computed exists
AND r.computed IS NULL THEN -- this computed is null
r.computed := r0.computed + 1; -- your computation here
END IF;
RETURN NEXT r;
r0 := r; -- remember last row
END LOOP;
END
$func$;
CREATE FUNCTION
-- call
SELECT * FROM f_compute();
| symbol | date | close | computed |
|---|---|---|---|
| TBBB | 2024-02-22 14:30:00+00 | 19.05 | null |
| TBBB | 2024-02-23 14:30:00+00 | 19.55 | null |
| TBBB | 2024-02-24 14:30:00+00 | 20.6 | null |
| TBBB | 2024-02-25 14:30:00+00 | 21.3 | null |
| TBBB | 2024-02-26 14:30:00+00 | 20.43 | 20.4 |
| TBBB | 2024-02-27 14:30:00+00 | 20.21 | 21.4 |
| TBBB | 2024-02-28 14:30:00+00 | 20.74 | 22.4 |
| TBBB | 2024-02-29 14:30:00+00 | 20.09 | 23.4 |
| TBBB | 2024-03-01 14:30:00+00 | 20.79 | 24.4 |
| TBBB | 2024-03-02 14:30:00+00 | 20.87 | 25.4 |
| TBBB | 2024-03-03 14:30:00+00 | 20.69 | 26.4 |
| TBBB | 2024-03-04 14:30:00+00 | 20.19 | 27.4 |
| TBBB | 2024-03-05 14:30:00+00 | 20.9 | 5 |
| TBBB | 2024-03-06 14:30:00+00 | 20.99 | 6 |
| TBBB | 2024-03-07 14:30:00+00 | 21.28 | 7 |
| TBBB | 2024-03-08 14:30:00+00 | 21.27 | 8 |
SELECT 16