Dear hackers,
 
Do you think there would be any use for an aggregate which returns an
array of the aggregated (usually simple) type?  Has this already been
done by anyone?  I looked at the source and noticed that for each
inserted item, the array utility functions perform a deep copy of the
entire array (plus a reallocation).  Normally, this is no big deal, but
if executed in the query stage, it could be kind of slow.  I also
noticed that null values inside is an item on the todo list.  Is anybody
currently working on this?
 
Merlin














Dear hackers,
 
Do you think there would be any use for an aggregate which
returns an array of the aggregated (usually simple) type?  Has this already been done by anyone?  I looked at the source and noticed that
for each inserted item, the array utility functions perform a deep copy of the
entire array (plus a reallocation).  Normally, this is no big deal, but if
executed in the query stage, it could be kind of slow.  I also noticed that null values inside
is an item on the todo list.  Is anybody currently working on this?
 
Merlin
Mon, 28 Jul 2003 17:21:45 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
an aggregate array function
Merlin Moncure wrote:
> Dear hackers,
>  
> Do you think there would be any use for an aggregate which returns an
> array of the aggregated (usually simple) type?  Has this already been
> done by anyone?  I looked at the source and noticed that for each
> inserted item, the array utility functions perform a deep copy of the
> entire array (plus a reallocation).  Normally, this is no big deal, but
> if executed in the query stage, it could be kind of slow.

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.

There is a contrib (intagg) that avoids the deep copy by passing 
pointers as integers, but I don't think that would be accepted as a 
builtin solution. I've considered maybe using a hash table to register 
valid pointers, but have not thought too hard about it yet. In any case 
it won't happen 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.

> I also noticed that null values inside is an item on the todo list.  Is anybody
> currently working on this?
>  

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

Joe



Mon, 28 Jul 2003 16:10:47 -0700
Joe Conway <mail@joeconway.com>
Re: an aggregate array function
Merlin Moncure wrote:

> Dear hackers,
>
>  
>
> Do you think there would be any use for an aggregate which returns an 
> array of the aggregated (usually simple) type?  Has this already been 
> done by anyone?  I looked at the source and noticed that for each 
> inserted item, the array utility functions perform a deep copy of the 
> entire array (plus a reallocation).  Normally, this is no big deal, 
> but if executed in the query stage, it could be kind of slow.  I also 
> noticed that null values inside is an item on the todo list.  Is 
> anybody currently working on this?
>
>  
>
> Merlin
>
I got another question to this:
why does PostgreSQL support Arrays in the first place?
For my taste, its a very messy concept...

Regards,
Dani


Tue, 29 Jul 2003 17:23:22 +0200
Dani Oderbolz <oderbolz@ecologic.de>
Re: an aggregate array function

It's in the SQL99 standard. There's nothing forcing you to use them - I 
am a (possibly) old-fashioned data architect, so I never use them ;-)

SQL99 actually allows you to use more or less arbitrary composite types 
as columns (although Pg currently doesn't) - many would argue that this 
violates first normal form. OTOH there's probably a good case to make 
that all this is necessary to provide good support to ObjectRelational 
mappings, and other OO stuff.

andrew

Dani Oderbolz wrote:

> I got another question to this:
> why does PostgreSQL support Arrays in the first place?
> For my taste, its a very messy concept...
>
> Regards,
> Dani
>
>



Tue, 29 Jul 2003 12:02:46 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: an aggregate array function