Re: GroupAggregate and Integer Arrays

[David Osborne <david@xxxxxxxxxxx>]

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.janes@xxxxxxxxx> wrote:

On Fri, Oct 23, 2015 at 7:29 AM, David Osborne <david@xxxxxxxxxxx> 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