I have the following dataset:

Schema (PostgreSQL v15)

Generate data

SELECT id, value, expected_output, comment
FROM (VALUES 
      (1, 0.89, 0.89, ''), 
      (2, 0.17, 0.17, ''), 
      (3, -0.30, 0, 'accum = -0.30'), 
      (4, -0.36, 0, 'accum = -0.30 - 0.36 = -0.66'), 
      (5, 1.41, 0.75, '0.75 = 1.41 - 0.66; accum = 0'), 
      (6, -0.49, 0, 'accum = -0.49'),
      (7, 0.24, 0, '0, because 0.24 - 0.49 = -0.25 is still negative, so accum = -0.25'), 
      (8, 6.15, 5.9, '5.9 = 6.15 - 0.25; accum = 0'), 
      (9, 6.05, 6.05, '')
) 
AS t(id, value, expected_output, comment);

And I want to generate the expected output using a function. The idea is that negative values should be accumulated, and then discounted whenever a positive value occurs.

I tried creating an aggregate that conditionally sums negative values only, and then I used that as a window function. The state function of the aggregate I came up with is this:

CREATE OR REPLACE FUNCTION public.sum_if_less_accum(
    _accumulated anyelement,
    _current anyelement)
    RETURNS anyelement
    LANGUAGE 'plpgsql'
AS $BODY$
begin
    IF _current < 0 OR _accumulated < 0THEN
        RETURN _accumulated + _current;
    ELSIF _current > _accumulated THEN
        return 0;
    END IF;
    
    RETURN _accumulated;

end
$BODY$;

And then I tried using that with some CASE WHENs, but I couldn’t get to the expected result. Is there a way to directly create an aggregate that does what I want?