--sfunc( internal-state, next-data-values ) ---> next-internal-state
CREATE OR REPLACE FUNCTION sum_transfn (state int4 , n int4)
    RETURNS int4
    AS $$
    new_state int4;
    RAISE NOTICE 'sum_transfn called with state: %, n=%' , state , n;
    IF state IS NULL THEN
        RAISE NOTICE 'compute where state is null';
            new_state := n;
            RAISE NOTICE 'new_state: %' , new_state;
            RETURN new_state;
        END IF;
        RETURN NULL;
        RAISE NOTICE 'compute where n =  %' , n;
        RAISE NOTICE 'state = %' , state;
        state := state + n;
        RAISE NOTICE 'now state = %' , state;
        RETURN state;
    END IF;
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);


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?
  • What exactly is your question? It must be a single question that can be understood. Jun 21 at 11:39
  • the state transition function sum_transfn (state int4 , n int4) output will become the next input of sum_transfn's state. This part is not that intuitive for me. @LaurenzAlbe
    – Mark
    Jun 21 at 15:41

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

  • I main issue is in point3. when the function return one value as the next function call first argument: state. But the function require two argument. why it assign to the first argument. In here point3 is why the returns value assigned to state not n? \
    – Mark
    Jun 22 at 4:48
  • That's too poor English for me to understand. The function does not assign to an argument. The code that calls the state transition function feeds the result from the previous invocation as state to the next invocation of the function. This is done by the query executor. Jun 22 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.