begin work;
create type avg_state as (total bigint, count bigint);

create or replace function avg_transfn(state avg_state, n bigint) returns avg_state as
$$
declare new_state avg_state;
begin
	if state is null then
		if n is not null then
			new_state.total := n;
			new_state.count := 1;
			return new_state;
		end if;
		return null;
	elsif n is not null then
		state.total := state.total + n;
		state.count := state.count + 1;
		return state;
	end if;

	return null;
end
$$ language plpgsql;

create or replace function avg_combine(state avg_state, state2 avg_state) returns avg_state as
$$
declare new_state avg_state;
begin
	if state is null then
		if state2 is not null then
			new_state.total := state2.total;
			new_state.count := state2.count;
			return new_state;
		end if;
		return null;
	elsif state2 is not null then
		state.total := state.total + state2.total;
		state.count := state.count + state2.count;
		return state;
	end if;

	return null;
end
$$ language plpgsql;

create function avg_finalfn(state avg_state) returns bigint as
$$
begin
	if state is null then
		return NULL;
	else
		return state.total / state.count;
	end if;
end
$$ language plpgsql;

create function sum_finalfn(state avg_state) returns bigint as
$$
begin
	if state is null then
		return NULL;
	else
		return state.total;
	end if;
end
$$ language plpgsql;

create aggregate my_avg(bigint)
(
   stype = avg_state,
   sfunc = avg_transfn,
   finalfunc = avg_finalfn,
   combinefunc = avg_combine
);

create aggregate my_sum(bigint)
(
   stype = avg_state,
   sfunc = avg_transfn,
   finalfunc = sum_finalfn,
   combinefunc = avg_combine
);

commit;

create table t (num int not null, one int not null, ten int not null, hundred int not null, thousand int not null, tenk int not null, hundredk int not null, million int not null);
insert into t select x,1,x%10+1,x%100+1,x%1000+1,x%10000+1,x%100000+1,x%1000000 from generate_series(1,10000000)x(x);

set max_parallel_degree = 8;
explain select my_sum(num) from t;

select my_sum(num) from t;