Re: Looking for a way to sum integer arrays....

Thank you for the responses!

To recap: pl/r array support works very well. In my case, I am looking
for pl/pgsql solution.

I also got this nice function from dennisb on the #postgresql irc
channel, which seems extremely "clean" and works with 7.4/8.0. My
original function didn't handle a blank initcond in the aggregate
gracefully.

CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS ' DECLARE   x ALIAS FOR $1;   y ALIAS FOR $2;   a
int;  b int;   i int;   res int[]; BEGIN   res = x; 
   a := array_lower (y, 1);   b := array_upper (y, 1);
   IF a IS NOT NULL THEN     FOR i IN a .. b LOOP       res[i] := coalesce(res[i],0) + y[i];     END LOOP;   END IF;
   RETURN res; END;
'
LANGUAGE plpgsql STRICT IMMUTABLE;

--- then this aggregate lets me sum integer arrays...

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


Here's how my sample table looked  and my new array summing aggregate
and function:

#SELECT * FROM arraytest ;id | somearr
----+---------a  | {1,2,3}b  | {0,1,2}
(2 rows)

#SELECT sum_integer_array(somearr) FROM arraytest ;sum_integer_array
-------------------{1,3,5}
(1 row)


Tony Wasson