aggreagte sfunc implict assignment?

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:

  1. There is two return null;. first return 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?
  2. 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.
  3. 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.

  1. The state of the aggregate function is initialized to NULL (since there is no INITCOND).

  2. The (virtual) table starts to get scanned, the first row returned contains 1.

  3. The SFUNC is called with n = 1 and state = NULL, resulting in 1. This result is assigned to the state.

  4. The table is scanned for the next row, which is 2.

  5. The SFUNC is called with n = 2 and state = 1, resulting in 3, which is assigned to the state.

  6. Steps 4 and 5 are repeated for the next two table rows, after which the state is 10.

  7. 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

Tags:

Leave a Reply