Thread: intagg
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
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.9I have created the extension as such "CREATE EXTENSION intagg"Then tried to use the function int_array_aggregate.Returns this messagefunction int_array_aggregate(integer[]) does not existselect int_array_aggregate(transactions) from xx beingcreate table x (transactions int4[]);Can anyone please advise..ThanksAndrew Bartley
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