> -----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
