Re: GroupAggregate and Integer Arrays

Ah yes sorry:

I think these cover it...

CREATE AGGREGATE sum (
sfunc = array_add,
basetype = INTEGER[],
stype = INTEGER[],
initcond = '{}'
);

CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS $$
-- Add two arrays.
select
ARRAY (
SELECT coalesce($1[i],0) + coalesce($2[i],0)
FROM (
select generate_series(least(array_lower($1, 1),array_lower($2,
1)), greatest(array_upper($1, 1),array_upper($2, 1)), 1) AS i
) sub
GROUP BY i
ORDER BY i
);
$$ LANGUAGE sql STRICT IMMUTABLE;

On 23 October 2015 at 17:15, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Oct 23, 2015 at 7:29 AM, David Osborne <david(at)qcode(dot)co(dot)uk> wrote:
>
>
>> Hi,
>>
>> Wondering if anyone could suggest how we could improve the performance of
>> this type of query?
>> The intensive part is the summing of integer arrays as far as I can see.
>>
>
>
> Postgres does not ship with any 'sum' function which takes array arguments.
>
> > select sum('{1,2,3,4,5,6}'::int[]);
>
> ERROR: function sum(integer[]) does not exist
>
> Are you using a user defined function? If so, how did you define it?
>
> Cheers,
>
> Jeff
>