Re: Aggeregate funtion calculating the average value of each same index of an array column in a table

On 2012-11-16, LEA KANG <makang71(at)gmail(dot)com> wrote:
> Hi,
>
> I have a table with several lines as following;
>
> - Create table mytable (type number , values integer [2]) ;
>
> - Insert into mytable values (1, ‘{ 10, 0 }’ );
> - Insert into mytable values (1, ‘{ 20, 30 }’ );
> - Insert into mytable values (2, ‘{30, 60}’ );
>
> (In fact, the array size is very big (ex. values [10000]) but the size is
> fix. In order to simplify the example, I used an array integer [2]).

>
> I would like to obtain the average value of each index of the array column,
> values column.
>
> Is it possible to create an aggregate function which can works as following
> ? :

yes it's absolutely possible, see "create aggregate" in the manual.

last time I looked arrays were O(n^2) in plpgsql, so don't use that
language if performance is important.

may want to use bigint[] for the accumulaqtor array and possibly
float[] for the result.

--
⚂⚃ 100% natural