intagg

Sorry that should be aggregate int_array_aggregate not function

On 20 June 2013 08:16, Andrew Bartley <ambartley@gmail.com> wrote:

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general