Pairwise array sum aggregate function?

From: François Beausoleil <francois(at)teksol(dot)info>
To: Forums postgresql <pgsql-general(at)postgresql(dot)org>
Subject: Pairwise array sum aggregate function?
Date: 2014-07-28 14:09:04
Message-ID: BFABC9D5-D791-428E-95B9-8DB690E74711@teksol.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

NOTE: Also posted to StackOverflow: http://stackoverflow.com/questions/24997131/pairwise-array-sum-aggregate-function

I have a table with arrays as one column, and I want to sum the array elements together:

> create table regres(a int[] not null);
> insert into regres values ('{1,2,3}'), ('{9, 12, 13}');
> select * from regres;
a
-----------
{1,2,3}
{9,12,13}

I want the result to be:

{10, 14, 16}

that is: {1 + 9, 2 + 12, 3 + 13}.

Does such a function already exist somewhere? The intagg extension looked like a good candidate, but such a function does not already exist.

The arrays are expected to be between 24 and 31 elements in length, all elements are NOT NULL, and the arrays themselves will also always be NOT NULL. All elements are basic int. There will be more than two rows per aggregate.

My implementation target is:

PostgreSQL 9.1.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Thanks!
François

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2014-07-28 14:20:26 Re: Checkpoint_segments optimal value
Previous Message Tom Lane 2014-07-28 13:56:04 Re: Re: [GENERAL] pg_dump behaves differently for different archive formats