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