Обсуждение: intagg

On Thu, Jun 20, 2013 at 12:22 AM, Andrew Bartley <ambartley@gmail.com> wrote:
> Sorry that should be aggregate int_array_aggregate not function
>
>
> On 20 June 2013 08:16, Andrew Bartley <ambartley@gmail.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)

Andrew Bartley <ambartley@gmail.com> writes:
> function int_array_aggregate(integer[]) does not exist

int_array_aggregate() takes integers, not arrays of integers.

Depending on exactly what semantics you'd like to have, you could
probably build a custom aggregate to do this without any new C code
--- try basing it on array_cat() for instance.

regression=# create aggregate myagg (anyarray) (
sfunc = array_cat,
stype = anyarray,
initcond = '{}');
CREATE AGGREGATE
regression=# select * from x;
 transactions
--------------
 {1,2}
 {3,4,5}
(2 rows)

regression=# select myagg(transactions) from x;
    myagg
-------------
 {1,2,3,4,5}
(1 row)

            regards, tom lane