DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=pchEtJOuaOZ/3oY8GfuC2GVfryBZsGjQXoIWYyR0AIsoXJDd3JMl+zeBJorwcbtrTqjpOfsRMbxV6SDYUf4QKM10574Qn5C8dUUL2qt/d+kuPEZSFOsZon11dBYjw8Gqc77Ere2R9xXiOz2NmB15kxrfcNDrmxhZt9HnWd3CoSE=
Message-ID: <6d8daee30504211821515e7f11@mail.gmail.com>
Date: Thu, 21 Apr 2005 18:21:27 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Looking for a way to sum integer arrays....
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 :=3D '''';
    i           INTEGER;
    nextnum     INTEGER;
BEGIN
    FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)
    LOOP
        RAISE NOTICE ''looking at element %'',i;
        nextnum :=3D COALESCE(inta1[i],0) + COALESCE(inta2[i],0);
        RAISE NOTICE ''nextnum %'',nextnum;
        out_arr_text :=3D 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) =3D  '','' THEN
        out_arr_text :=3D substring(out_arr_text,1,length(out_arr_text)-1);
    END IF;
    out_arr_text :=3D ''{'' || out_arr_text || ''}'';
    RAISE NOTICE ''text %'',out_arr_text;
    out_arr :=3D 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 =3D sum_intarray,
    basetype =3D INTEGER[],
    stype =3D INTEGER[],
    initcond =3D '{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