SQLAlchemy sum function with bounds, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table foo(
  id        serial  primary key,
  timestamp int     not null,
  points    numeric not null
);

insert into foo(timestamp, points)
values (1,   75),
       (2,   50),
       (3, -100),
       (4,  -50),
       (5,  -75);

select timestamp,
       points,
       sum(points) over (order by timestamp) running_total_unbounded
from   foo;

select timestamp,
       points,
       least(greatest(sum(points) over (order by timestamp), -100), 100) running_total_bounded_after_summing
from   foo;

with recursive t as (
  (select   *, points running_total
   from     foo
   order by timestamp
   limit    1)
  union all
  (select   foo.*, least(greatest(t.running_total + foo.points, -100), 100)
   from     foo, t
   where    foo.timestamp > t.timestamp
   order by foo.timestamp
   limit    1)
)
select timestamp,
       points,
       running_total running_total_bounded_per_row_with_cte
from   t;

create function bounded_add(int_state anyelement, next_value anyelement, next_min anyelement, next_max anyelement)
  returns anyelement
  immutable
  language sql
as $func$
  select least(greatest(int_state + next_value, next_min), next_max);
$func$;

create aggregate bounded_sum(next_value anyelement, next_min anyelement, next_max anyelement)
(
    sfunc    = bounded_add,
    stype    = anyelement,
    initcond = '0'
);

select timestamp,
       points,
       bounded_sum(points, -100.0, 100.0) over (order by timestamp) running_total_bounded_with_custom_aggregate
from   foo;


absolute service time: 0,39 sec