Обсуждение: 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