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