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:
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 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.
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 by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0