Sean
----- Original Message ----- From: "Tony Wasson" <[EMAIL PROTECTED]>
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
in advance to anyone who reads this far.Tony [EMAIL PROTECTED](end of
broadcast)---------------------------TIP 3: if posting/reading through Usenet,
please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]
so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
