Joe Conway wrote:
> > Do you think there would be any use for an aggregate which returns
an
> > array of the aggregated (usually simple) type?  

>What exactly have you looked at? In current cvs there is array_append 
>and array_cat. There *was* array_accum, but that was yanked due to an 
>objection that it was redundant with the other two.

Actually, I was looking at array_set, which has a provision to grow a 1d
array.  I was looking at the 7.3.2 sources, so it's pretty clear I would
have to look at something newer.  I'll wait for 7.4.

>BTW, I tried array_accum() (which is not really much different than 
>array_append()) with groups of about 10,000 elements and feeding the 
>array into a plr final function for a mean calculation. It was for sure

>slow compared to a native AVG() aggregate, but it wasn't that bad 
>either. I don't remember the specifics, but it would be easy enough to 
>try it out for yourself.

Well, if dynamic growth is expected, there are some easy optimizations
that could reduce the time spent reallocating the array.  If there was a
detectable difference vs. the avg() function, which performs zero
reallocations, its probably worthwhile.

Based on what I saw with the older source, I assumed there was little or
no dynamic growth in normal use.

> No one is currently working on it that I'm aware of, but I was 
> considering working on it for 7.5

By all means!  What do you think about the other question about an
'array creating aggregate', is that a useful contribution?

Joe



Tue, 29 Jul 2003 08:17:44 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: an aggregate array function
Merlin Moncure wrote:
> What do you think about the other question about an
> 'array creating aggregate', is that a useful contribution?
> 

Hmm, either I'm not understanding you, or you're not understanding me ;-)

First, see contrib/intagg.
Second, the following works in 7.4devel:

-- create test data for polymorphic aggregates
create table t(f1 int, f2 float, f3 float);
insert into t values(1,11.1,21.1);
insert into t values(1,11.2,21.2);
insert into t values(1,11.3,21.3);
insert into t values(2,12.1,22.1);
insert into t values(2,12.2,22.2);
insert into t values(2,12.3,22.3);
insert into t values(3,13.1,23.1);
insert into t values(3,13.2,23.2);

CREATE AGGREGATE myagg1
(
   BASETYPE = float8,
   SFUNC = array_append,
   STYPE = float8[],
   INITCOND = '{}'
);

CREATE AGGREGATE myagg2
(
   BASETYPE = float8[],
   SFUNC = array_cat,
   STYPE = float8[],
   INITCOND = '{}'
);

regression=# select f1, myagg1(f2) from t group by f1;
  f1 |      myagg1
----+------------------
   3 | {13.1,13.2}
   2 | {12.1,12.2,12.3}
   1 | {11.1,11.2,11.3}
(3 rows)

regression=# select f1, myagg2(array[f2,f3]) from t group by f1;
  f1 |                myagg2
----+---------------------------------------
   3 | {{13.1,23.1},{13.2,23.2}}
   2 | {{12.1,22.1},{12.2,22.2},{12.3,22.3}}
   1 | {{11.1,21.1},{11.2,21.2},{11.3,21.3}}
(3 rows)

Joe


Tue, 29 Jul 2003 07:25:37 -0700
Joe Conway <mail@joeconway.com>
Re: an aggregate array function