Thread: Looking for a way to sum integer arrays....
I'd like to be able to sum up an integer array. Like so: {3,2,1} + {0,2,2} ------- {3,4,3} The following solution I've been hacking on works, although I think it is far from "ideal". Is there a built in way to sum up arrays? If not, is there a better way than my crude method? I have tested this on 7.4 and 8.0. I'd also be appreciate if any insight on why my aggregate fails to work when I have an empty initcondition. P.S. I have never written an aggregate and I was lost trying to follow the complex_sum example in the docs. --------------------------------------------- CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS INTEGER[] LANGUAGE 'plpgsql' AS ' /* || Author: Tony Wasson || || Overview: Experiment with arrays and aggregates || 3,2,1 || + 0,2,2 || ------- || 3,4,3 || || Revisions: (when, who, what) || 2005/04/21 -- TW - Create function */ DECLARE inta1 ALIAS FOR $1; inta2 ALIAS FOR $2; out_arr INTEGER[]; out_arr_text TEXT := ''''; i INTEGER; nextnum INTEGER; BEGIN FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1) LOOP RAISE NOTICE ''looking at element %'',i; nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0); RAISE NOTICE ''nextnum %'',nextnum; out_arr_text :=out_arr_text || nextnum::TEXT || '',''; RAISE NOTICE ''text %'',out_arr_text; END LOOP; RAISE NOTICE ''text %'',out_arr_text; --drop the last comma IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1); END IF; out_arr_text := ''{'' || out_arr_text || ''}''; RAISE NOTICE ''text %'',out_arr_text; out_arr := out_arr_text; RAISE NOTICE ''out_arr %'',out_arr; RETURN out_arr; END '; SELECT sum_intarray('{1,2}','{2,3}'); SELECT sum_intarray('{3,2,1}','{0,2,2}'); --- Now I make a table to demonstrate an aggregate on CREATE TABLE arraytest ( id character varying(10) NOT NULL, somearr integer[] ); INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}'); INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}'); CREATE AGGREGATE sum_integer_array ( sfunc = sum_intarray, basetype = INTEGER[], stype = INTEGER[], initcond = '{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}' ); ---------------------- # SELECT sum_integer_array(somearr) FROM arraytest; sum_integer_array ---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} Thanks in advance to anyone who reads this far. Tony Wasson ajwasson@gmail.com
You would definitely want to look into using pl/R for this. Also, other procedure languages (perl, for example) work well with arrays so may be easier to use for this situation. As for the aggregate, I don't know how to make that more dynamic in terms of return value. Sean ----- Original Message ----- From: "Tony Wasson" <ajwasson@gmail.com> To: <pgsql-sql@postgresql.org> Sent: Thursday, April 21, 2005 9:21 PM Subject: [SQL] Looking for a way to sum integer arrays.... I'd like to be able to sum up an integer array. Like so: {3,2,1} + {0,2,2} ------- {3,4,3} The following solution I've been hacking on works, although I think it is far from "ideal". Is there a built in way to sum up arrays? If not, is there a better way than my crude method? I have tested this on 7.4 and 8.0. I'd also be appreciate if any insight on why my aggregate fails to work when I have an empty initcondition. P.S. I have never written an aggregate and I was lost trying to follow the complex_sum example in the docs. --------------------------------------------- CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS INTEGER[] LANGUAGE 'plpgsql' AS ' /* || Author: Tony Wasson || || Overview: Experiment with arrays and aggregates || 3,2,1 || + 0,2,2 || ------- || 3,4,3 || || Revisions: (when, who, what) || 2005/04/21 -- TW - Create function */ DECLARE inta1 ALIAS FOR $1; inta2 ALIAS FOR $2; out_arr INTEGER[]; out_arr_text TEXT := ''''; i INTEGER; nextnum INTEGER; BEGIN FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1) LOOP RAISE NOTICE ''looking at element %'',i; nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0); RAISE NOTICE ''nextnum %'',nextnum; out_arr_text :=out_arr_text || nextnum::TEXT || '',''; RAISE NOTICE ''text %'',out_arr_text; END LOOP; RAISE NOTICE ''text %'',out_arr_text; --drop the last comma IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1); END IF; out_arr_text := ''{'' || out_arr_text || ''}''; RAISE NOTICE ''text %'',out_arr_text; out_arr := out_arr_text; RAISE NOTICE ''out_arr %'',out_arr; RETURN out_arr; END '; SELECT sum_intarray('{1,2}','{2,3}'); SELECT sum_intarray('{3,2,1}','{0,2,2}'); --- Now I make a table to demonstrate an aggregate on CREATE TABLE arraytest ( id character varying(10) NOT NULL, somearr integer[] ); INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}'); INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}'); CREATE AGGREGATE sum_integer_array ( sfunc = sum_intarray, basetype = INTEGER[], stype = INTEGER[], initcond = '{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}' ); ---------------------- # SELECT sum_integer_array(somearr) FROM arraytest; sum_integer_array ---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}Thanks inadvance to anyone who reads this far.Tony Wassonajwasson@gmail.com---------------------------(end of broadcast)---------------------------TIP3: if posting/reading through Usenet, please send an appropriate subscribe-nomailcommand to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
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
Hi, CREATE OR REPLACE FUNCTION SUM_ARR( aArr1 Integer[] , aArr2 Integer[] ) RETURNS Integer[] AS $$ DECLARE aRetu Integer[]; BEGIN -- Initialize the Return array with first array value. FOR i IN array_lower( aArr1 )..array_upper( aArr1 ) LOOP array_append( aRetu , aArr1[i] ); END LOOP; -- Add the second array value to return array FOR i IN array_lower( aArr2 )..array_upper( aArr2 ) LOOP if i > array_upper( aRetu ) then array_append( aRetu , aArr2[i]); else aRetu[i] = aRetu[i]+aArr2[i]; end; END LOOP; RETURN aRetu; END $$ LANGUAGE 'plpgsql' Regards, R.Muralidharan -----Original Message----- From: Tony Wasson [mailto:ajwasson@gmail.com] Sent: Friday, April 22, 2005 6:51 AM To: pgsql-sql@postgresql.org Subject: [SQL] Looking for a way to sum integer arrays.... I'd like to be able to sum up an integer array. Like so: {3,2,1} + {0,2,2} ------- {3,4,3} The following solution I've been hacking on works, although I think it is far from "ideal". Is there a built in way to sum up arrays? If not, is there a better way than my crude method? I have tested this on 7.4 and 8.0. I'd also be appreciate if any insight on why my aggregate fails to work when I have an empty initcondition. P.S. I have never written an aggregate and I was lost trying to follow the complex_sum example in the docs. --------------------------------------------- CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS INTEGER[] LANGUAGE 'plpgsql' AS ' /* || Author: Tony Wasson || || Overview: Experiment with arrays and aggregates || 3,2,1 || + 0,2,2 || ------- || 3,4,3 || || Revisions: (when, who, what) || 2005/04/21 -- TW - Create function */ DECLARE inta1 ALIAS FOR $1; inta2 ALIAS FOR $2; out_arr INTEGER[]; out_arr_text TEXT := ''''; i INTEGER; nextnum INTEGER; BEGIN FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1) LOOP RAISE NOTICE ''looking at element %'',i; nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0); RAISE NOTICE ''nextnum %'',nextnum; out_arr_text :=out_arr_text || nextnum::TEXT || '',''; RAISE NOTICE ''text %'',out_arr_text; END LOOP; RAISE NOTICE ''text %'',out_arr_text; --drop the last comma IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1); END IF; out_arr_text := ''{'' || out_arr_text || ''}''; RAISE NOTICE ''text %'',out_arr_text; out_arr := out_arr_text; RAISE NOTICE ''out_arr %'',out_arr; RETURN out_arr; END '; SELECT sum_intarray('{1,2}','{2,3}'); SELECT sum_intarray('{3,2,1}','{0,2,2}'); --- Now I make a table to demonstrate an aggregate on CREATE TABLE arraytest ( id character varying(10) NOT NULL, somearr integer[] ); INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}'); INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}'); CREATE AGGREGATE sum_integer_array ( sfunc = sum_intarray, basetype = INTEGER[], stype = INTEGER[], initcond = '{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}' ); ---------------------- # SELECT sum_integer_array(somearr) FROM arraytest; sum_integer_array ---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} Thanks in advance to anyone who reads this far. Tony Wasson ajwasson@gmail.com ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.orgso that your message can get through to the mailing list cleanly