All we need is an easy explanation of the problem, so here it is.
--sfunc( internal-state, next-data-values ) ---> next-internal-state 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 my_sum(int4) ( stype = int4, sfunc = sum_transfn );
call the aggregate:
select my_sum(one)from (values (1),(2),(3),(4)) t(one);
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;. first
return nullmeans 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 internal-state 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.
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
The (virtual) table starts to get scanned, the first row returned contains 1.
SFUNCis called with
n = 1and
state = NULL, resulting in 1. This result is assigned to the state.
The table is scanned for the next row, which is 2.
SFUNCis called with
n = 2and
state = 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 🙂