From: Tony Wasson <ajwasson@gmail.com>
To: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Looking for a way to sum integer arrays....
Date: Mon, 25 Apr 2005 08:51:40 -0700
Message-ID: <6d8daee305042508514a50fd4e@mail.gmail.com> (raw)
In-Reply-To: <02767D4600E59A4487233B23AEF5C59922C29A@blrmail1.aus.pervasive.com>
References: <02767D4600E59A4487233B23AEF5C59922C29A@blrmail1.aus.pervasive.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajwasson@gmail.com, ramakrishnanm@pervasive-postgres.com
Subject: Re: Looking for a way to sum integer arrays....
In-Reply-To: <6d8daee305042508514a50fd4e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox