All we need is an easy explanation of the problem, so here it is.
sfunc( internalstate, nextdatavalues ) > nextinternalstate
CREATE OR REPLACE FUNCTION sum_transfn (state int4 , n int4)
RETURNS int4
AS $$
DECLARE
new_state int4;
BEGIN
RAISE NOTICE 'sum_transfn called with state: %, n=%' , state , n;
IF state IS NULL THEN
RAISE NOTICE 'compute where state is null';
IF n IS NOT NULL THEN
new_state := n;
RAISE NOTICE 'new_state: %' , new_state;
RETURN new_state;
END IF;
RETURN NULL;
ELSIF n IS NOT NULL THEN
RAISE NOTICE 'compute where n = %' , n;
RAISE NOTICE 'state = %' , state;
state := state + n;
RAISE NOTICE 'now state = %' , state;
RETURN state;
END IF;
RETURN NULL;
END
$$
LANGUAGE plpgsql;
create aggregate:
create aggregate my_sum(int4)
(
stype = int4,
sfunc = sum_transfn
);
call the aggregate:
select my_sum(one)from (values (1),(2),(3),(4)) t(one);
return
NOTICE: sum_transfn called with state: <NULL>, n=1
NOTICE: compute where state is null
NOTICE: new_state: 1
NOTICE: sum_transfn called with state: 1, n=2
NOTICE: compute where n = 2
NOTICE: state = 1
NOTICE: now state = 3
NOTICE: sum_transfn called with state: 3, n=3
NOTICE: compute where n = 3
NOTICE: state = 3
NOTICE: now state = 6
NOTICE: sum_transfn called with state: 6, n=4
NOTICE: compute where n = 4
NOTICE: state = 6
NOTICE: now state = 10
++
 my_sum 
++
 10 
++
In high level I get it by using raise notice. But overall many parts I did’t get i. Like:
 There is two
return null;
. firstreturn null
means that if state is null and n is null then return null? The second null means that state is not null and n is null then return null?  my_sum(one) only one iput argument, So it’s explicit assigned that state is null, n = one? I kind of get it. Since manual mentioned: If it is not supplied then the state value starts out null.
if state is null then .... return new_state;
so the aggregate sfunc will do implicitly assign the new_state to state, since state is internalstate value?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Perhaps it will become clearer if we examine what is going on step by step.

The state of the aggregate function is initialized to NULL (since there is no
INITCOND
). 
The (virtual) table starts to get scanned, the first row returned contains 1.

The
SFUNC
is called withn = 1
andstate = NULL
, resulting in 1. This result is assigned to the state. 
The table is scanned for the next row, which is 2.

The
SFUNC
is called withn = 2
andstate = 1
, resulting in 3, which is assigned to the state. 
Steps 4 and 5 are repeated for the next two table rows, after which the state is 10.

Since there is no
FINALFUNC
, the state becomes the result of the aggregate function.
That’s how aggregate functions are processed. The aggregate has only one argument because the state is an implementation detail.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc bysa 2.5, cc bysa 3.0 and cc bysa 4.0