Thread: More aggregate functions?

What do people think of adding some more aggregate functions. These are
the ones that MySQL has and PG doesn't:

- STD/STDDEV
- VARIANCE
- BIT_OR
- BIT_AND
- GROUP_CONCAT (for strings, added in MySQL 4.x)

Particularly, I find GROUP_CONCAT practical to quickly display 1-many
relationship, e.g.:

  CREATE TABLE mommy (
    id INT PRIMARY KEY,
    name TEXT);
  CREATE TABLE child (
    id INT PRIMARY KEY,
    mommy_id INT REFERENCES(mommy(id)),
    name TEXT
  );

  SELECT
    mommy.name as mommy,
    GROUP_CONCAT(child.name SEPARATOR ", ") as children
  FROM mommy, child
  GROUP BY mommy.id;

would result in:

  mommy         children
  -----         -----------------
  dot           ellen, catherine
  cate          bridget, kerry, rory
  rachel        emma

Btw, I have written 1 or 2 of the above myself with CREATE AGGREGATE.
But perhaps it would be nice if it's available as a builtin function or
at least present in contrib/.

--
dave

* David Garamond (lists@zara.6.isreserved.com) wrote:
> What do people think of adding some more aggregate functions. These are
> the ones that MySQL has and PG doesn't:
>
> - STD/STDDEV
> - VARIANCE
> - BIT_OR
> - BIT_AND
> - GROUP_CONCAT (for strings, added in MySQL 4.x)
[...]
> Btw, I have written 1 or 2 of the above myself with CREATE AGGREGATE.
> But perhaps it would be nice if it's available as a builtin function or
> at least present in contrib/.

There's an example of GROUP_CONCAT already in the mailing lists but
a more obvious place, linked from the documentation, would be really
nice.  Having the functions included in the main distribution would be
nice too, of course. :)  I'd be happy to host such a page if necessary
but I really think it'd be better on postgresql.org and linked in from
the documentation as 'other non-standard/extra functions' or some such.
What can we do to make this happen?

    Stephen

David Garamond <lists@zara.6.isreserved.com> writes:
> What do people think of adding some more aggregate functions. These are
> the ones that MySQL has and PG doesn't:

> - STD/STDDEV
> - VARIANCE

Have you read the documentation lately?

> - BIT_OR
> - BIT_AND
> - GROUP_CONCAT (for strings, added in MySQL 4.x)

Unless these are doing something I don't see, they should be easily
writable using CREATE AGGREGATE.

            regards, tom lane

stddev and variance are available for both float8 and
numeric datatypes.

> David Garamond <lists@zara.6.isreserved.com> writes:
>
>>What do people think of adding some more aggregate functions. These are
>>the ones that MySQL has and PG doesn't:
>
>
>>- STD/STDDEV
>>- VARIANCE

--
P. J. "Josh" Rovero                                 Sonalysts, Inc.
Email: rovero@sonalysts.com    www.sonalysts.com    215 Parkway North
Work: (860)326-3671 or 442-4355                     Waterford CT 06385
***********************************************************************

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > - BIT_OR
> > - BIT_AND
> > - GROUP_CONCAT (for strings, added in MySQL 4.x)
>
> Unless these are doing something I don't see, they should be easily
> writable using CREATE AGGREGATE.

It'd be nice to have a place to put functions that people have written
and found useful but that aren't part of the base.  Even nicer would be
to have this place be on postgresql.org and linked to from the
documentation.  I havn't needed BIT_OR or BIT_AND before but I've used
the GROUP_CONCAT-type aggregate a couple of times.  The first time I
wanted that function it took me a while to find (though eventually I did
in the mailing lists somewhere).

    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> It'd be nice to have a place to put functions that people have written
> and found useful but that aren't part of the base.

Agreed.  The "Postgres Cookbook" site used to do some of that, but
I don't think it's been maintained lately.  Right now I'd say
techdocs.postgresql.org is the most likely candidate for housing a
collection of small functions of this sort.

            regards, tom lane