# for user defined aggregate how to understand state transition function

Posted on

### Question :

``````CREATE AGGREGATE newavg (
sfunc = int4_avg_accum, basetype = int4, stype = _int8,
finalfunc = int8_avg,
initcond1 = '{0,0}'
);

select newavg(a) , pg_typeof(newavg(1)) from (values (1),(2),(3)) v(a);
``````

return:

``````+--------------------+-----------+
|       newavg       | pg_typeof |
+--------------------+-----------+
| 2.0000000000000000 | numeric   |
+--------------------+-----------+
``````

from the result, my conjecture is that newavg seems the same as avg.
to understand newavg, I need to understand int4_avg_accum, int8_avg.
after some test, I can guess what does int8_avg do.

``````select  int8_avg(array[6,3]); --return 0.50000000000000000000
select  int8_avg(array[3,6]); --return  2.0000000000000000
``````

now the problem is int4_avg_accum
My guess is that newavg aggregate, sfunc function compute and return array of integer then pipe/feed to the finalfunc. Finally finalfunc return the value.

test of int4_avg_accum:

``````select int4_avg_accum(a,0) from (values (array[1,1]),(array[1,2]),(array[1,3])) v(a);
``````

return:

``````+----------------+
| int4_avg_accum |
+----------------+
| {2,1}          |
| {2,2}          |
| {2,3}          |
+----------------+

select int4_avg_accum(a,0) from (values (array[1,1]),(array[2,1]),(array[3,1])) v(a);
``````

return:

``````+----------------+
| int4_avg_accum |
+----------------+
| {2,1}          |
| {3,1}          |
| {4,1}          |
+----------------+
``````

I found the int4_avg_accum source code, but i don’t C.

So overall the question is what does int4_avg_accum do? Based on the following query

``````select newavg(a) , pg_typeof(newavg(1)) from (values (1),(2),(3)) v(a);
``````

there must a step that state transition function return array[3,6]. How can I play around get array[3,6].