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