Re: intagg

On Thu, Jun 20, 2013 at 12:22 AM, Andrew Bartley <ambartley(at)gmail(dot)com> wrote:
> Sorry that should be aggregate int_array_aggregate not function
>
>
> On 20 June 2013 08:16, Andrew Bartley <ambartley(at)gmail(dot)com> wrote:
>>
>> Hi All,
>>
>> I am trying to use the intagg extension. in 9.1.9
>>
>> I have created the extension as such "CREATE EXTENSION intagg"
>>
>> Then tried to use the function int_array_aggregate.
>>
>> Returns this message
>>
>> function int_array_aggregate(integer[]) does not exist
>>
>> select int_array_aggregate(transactions) from x
>>
>> x being
>>
>> create table x (transactions int4[]);
>>
>> Can anyone please advise..
>>
>> Thanks
>>
>> Andrew Bartley
>
>

int_array_aggregate or (array_agg) needs int as input not int[]. You
can unnest first:

=> INSERT INTO x VALUES ('{4,5,6}');
INSERT 0 1
=> INSERT INTO x VALUES ('{1,20,30}');
INSERT 0 1
=> SELECT unnest(transactions) FROM x;
unnest
--------
4
5
6
1
20
30
(6 rows)

=> SELECT array_agg(i) FROM (SELECT unnest(transactions) from x) AS j(i);
array_agg
-----------------
{4,5,6,1,20,30}
(1 row)

=> SELECT array_agg(i ORDER BY i) FROM (SELECT unnest(transactions)
from x) AS j(i);
array_agg
-----------------
{1,4,5,6,20,30}
(1 row)

=> SELECT array_agg(i ORDER BY i) FROM (SELECT unnest(transactions)
from x) AS j(i) GROUP BY i % 2;
array_agg
-------------
{4,6,20,30}
{1,5}
(2 rows)