Postgres 12

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 my_table(id serial primary key, float_number float);
insert into my_table (float_number) 
select unnest(array[1.1, 1.2, 1.4, 1.2, 1.3, 1.4, 1.4])
7 rows affected

create type cr_type as (int_value int, fp_total float, int_total int);

drop function if exists cr_state(state cr_type, fp_value float);
create or replace function cr_state(state cr_type, fp_value float)
returns cr_type language plpgsql as $$
begin
    state.int_value := round(fp_value + state.fp_total) - state.int_total;
    state.fp_total := state.fp_total + fp_value;
    state.int_total := state.int_total + state.int_value;
    return state;
end $$;

drop function if exists cr_final(state cr_type);
create or replace function cr_final(state cr_type)
returns int language plpgsql as $$
declare
begin
    return state.int_value;
end $$;

create aggregate cascade_rounding_window(float) (
    sfunc = cr_state,
    stype = cr_type,
    finalfunc = cr_final,
    initcond = '(0, 0, 0)'
);
select 
	id, 
	float_number, 
	cascade_rounding_window(float_number) over (order by id) as int_number
from my_table;


id
float_number
int_number


1
1.1
1

2
1.2
1

3
1.4
2

4
1.2
1

5
1.3
1

6
1.4
2

7
1.4
1