DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=bZ9ISmYnnZu1mucf2FSi5w+KdmfuXTOBkNLSPGvDXX8gqyMlG852ZneOWUDnp/16ZRMEn0r6q8y9g0N9zp53hsyaFcqzHOYc/KF6t3xSzU89cRRnY3mvyPBogKFFSM69JQSughbxNQFr6nsLnWilsywkHQeQNMiT+Vn3oFcP6RQ=
Message-ID: <6d8daee305042508514a50fd4e@mail.gmail.com>
Date: Mon, 25 Apr 2005 08:51:40 -0700
From: Tony Wasson <ajwasson@gmail.com>
Reply-To: Tony Wasson <ajwasson@gmail.com>
To: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
Subject: Re: Looking for a way to sum integer arrays....
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <02767D4600E59A4487233B23AEF5C59922C29A@blrmail1.aus.pervasive.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <02767D4600E59A4487233B23AEF5C59922C29A@blrmail1.aus.pervasive.com>

Thank you for the responses!=20

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 =3D x;

    a :=3D array_lower (y, 1);
    b :=3D array_upper (y, 1);

    IF a IS NOT NULL THEN
      FOR i IN a .. b LOOP
        res[i] :=3D 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 =3D array_add,
    basetype =3D INTEGER[],
    stype =3D INTEGER[],
    initcond =3D '{}'
);


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