> -----Original Message-----
> From: mlw [mailto:pgsql@mohawksoft.com]
> Sent: Wednesday, March 05, 2003 3:47 PM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] Aggregate "rollup"
> 
> I had written a piece of code about two years ago that used the
> aggregate feature of PostgreSQL to create an array of integers from an
> aggregate, as:
> 
> select int_array_aggregate( column ) from table group by column
> 

Do I understand correctly that this still follows the normal rules for
grouping, so that only like values are put in the array?

Example: column has values 1,1,1,2,2 spread over 5 rows.
Your query returns two rows with row1={1,1,1} and row2 = {2,2}...is this
correct?

Also, what if your aggregate column is different from the group column: 
Table t with columns c1, c2 with 5 rows:
C1 C2
1, 1
1, 2
1, 3
2, 1
2, 2

Does select C1, int_array_aggregate( C2 ) from table group by C1 return

1, {1, 2, 3}
2, {1, 2}
??

FWIW, I think that's a pretty cool function.  This allows the backend to
telescope 1 dimension (only) out of a dataset, the most detailed one.
In certain situations with large datasets over slow connections, this
could be a big payoff.

Also, all this talk about XML has got me thinking about how to allow
basic query features to provide simple nesting services.  consider:

select C1, C2 from t for xml;  returns:
<t> 
<C1>1</C1><C2>1</C2>
<C1>1</C1><C2>2</C2>
<C1>1</C1><C2>3</C2>
<C1>2</C1><C2>1</C2>
<C1>2</C1><C2>2</C2>
</t>
select C1, xml_aggregate(C2) from t for xml; returns:
<t>
<C1 value="1"><C2>1</C2><C2>2</C2><C2>3</C2></C1>
<C1 value="2"><C2>1</C2><C2>2</C2><C2>3</C2></C1>
</t> 

> create table fast_lookup as select reference,
> int_array_aggregate(result) from table group by result
> 
> The question is, would a more comprehensive solution be wanted?
> Possible? Something like:
> 
> 
> Any thoughts? I think I need to fix the code in the current
> /contrib/intagg anyway, so is it worth doing the extra work to
included
> multiple data types?

Yes.

Just a thought.
Merlin


Thu, 6 Mar 2003 14:02:21 -0500
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: Aggregate "rollup"


Merlin Moncure wrote:

>>-----Original Message-----
>>From: mlw [mailto:pgsql@mohawksoft.com]
>>Sent: Wednesday, March 05, 2003 3:47 PM
>>To: pgsql-hackers@postgresql.org
>>Subject: [HACKERS] Aggregate "rollup"
>>
>>I had written a piece of code about two years ago that used the
>>aggregate feature of PostgreSQL to create an array of integers from an
>>aggregate, as:
>>
>>select int_array_aggregate( column ) from table group by column
>>
>>    
>>
>
>Do I understand correctly that this still follows the normal rules for
>grouping, so that only like values are put in the array?
>
>Example: column has values 1,1,1,2,2 spread over 5 rows.
>Your query returns two rows with row1={1,1,1} and row2 = {2,2}...is this
>correct?
>
Actually, it is more intended to put all the entries in a "one to many" 
table in a single column, as:

create table classic_one_to_many
(
    leftside    integer,
    rightside    integer
);

create table fast_lookup as select leftside, 
int_array_aggregate(rightside) from classic_one_to_many group by leftside;



Thu, 06 Mar 2003 14:59:00 -0500
mlw <pgsql@mohawksoft.com>
Re: Aggregate "rollup"