"Jonathan Neve" <jonathan@...> wrote:

>
> >>I had an idea of a new feature which I think could be useful, please
> >>tell me what you think. Currently, it's possible to aggregate a column
> >>as a sum, an average, an minimum or a maximum. It would be useful, in
> >>certain cirsumstances, to be able to aggregate a column by concatenating

> >>all the values, or perhaps by putting them all into an array.

Doable. Some RDBMS offer this feature. In fact, it's already implemented in
Yaffil and I think we could easily backport this code into FB3. This new
aggregate function is LIST (<value>, <separator>).

> > There are many problems with this.

Not so many, I think ;-)

> > 1) What is the output datatype for the SUM (this needs to be known at
> > prepare time)?

[VAR]CHAR (MAX_COLUMN_SIZE)

> How about an array? This would also make the individual elements easier
> to access, so that they can then be manipulated in any way necessary,
> not necessarily by concatenation.

No arrays, please. Don't forget they're implemented only partially and e.g.
not available in PSQL. To be honest, <flame on> I would prefer them to
become deprecated instead of explaining people why arrays cannot be used
here and are buggy there </flame>.

> >2) I do this on 1.000 records with a average of 500 characters per

record?

You'll get a runtime error (concatenation overflow). When we raise the row
size to be represented with a longword, this issue will go away.

> You could. It would probably still be faster than fetching 1000 records
> or 500 characters each, and doing it all on the client side.

The engine will still fetching all rows internally, so the only benefit you
get is lack of network transmission.

> But I think this would mostly be useful in cases where there are a small
> number of records being grouped together, because it would in such cases
> be a simple alternative to having a separate query for getting this
> information.

I'm not a great supporter of this feature, but I'm not going to object
either.


Dmitry