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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

* David Garamond (lists@zara.6.isreserved.com) wrote:[color=blue]
> 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)[/color]
[...][color=blue]
> 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/.[/color]

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFAc+wcrzgMPqB3kigRAlutAJ9/LfhBDdNSPThfUu1zT6j/RH4YYACfZmsf
pbRuY0NGZhylC6EGaoh2jUk=
=nbn8
-----END PGP SIGNATURE-----

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

Have you read the documentation lately?
[color=blue]
> - BIT_OR
> - BIT_AND
> - GROUP_CONCAT (for strings, added in MySQL 4.x)[/color]

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

stddev and variance are available for both float8 and
numeric datatypes.
[color=blue]
> David Garamond <lists@zara.6.isreserved.com> writes:
>[color=green]
>>What do people think of adding some more aggregate functions. These are
>>the ones that MySQL has and PG doesn't:[/color]
>
>[color=green]
>>- STD/STDDEV
>>- VARIANCE[/color][/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFAdCd4rzgMPqB3kigRAlMeAJ9JTu5fTIKDItHrZe1Hmn RFi/mpywCfbor7
bv47AP1E7Tw5/0uF/MWNjkI=
=4H5s
-----END PGP SIGNATURE-----

Stephen Frost <sfrost@snowman.net> writes:[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly