I would like to create an aggregate function that returns a concatenation
of grouped values. It would be particularly useful if I could pass an
optional delimiter into the aggregate function.
For example:
With a table, 'team'...
team_number member_name
--------------------------
1 David
1 Sanjay
1 Marie
2 Josh
2 Rani
...
...a query like:
SELECT team_number, aggregated_concat(member_name, ', ' ) AS members FROM
team GROUP BY team_number;
...would return:
team_number members
-----------------------------------
1 David, Sanjay, Marie
2 Josh, Rani
...
Here's what I've got so far:
/* For the default case, with no delimiter provided: */
CREATE FUNCTION concat(text, text) RETURNS text
AS 'select $1 || $2;'
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;
/* With a delimiter provided: */
CREATE FUNCTION concat(text, text, text) RETURNS text
AS 'select $1 || $3 || $2;'
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE aggregated_concat (
sfunc = concat,
basetype = text,
stype = text
);
My problem is I don't see how to make aggregated_concat accept an
optional delimiter argument.
Maybe it's not possible?
Any ideas?
Is there some completely different approach I should consider for
concatenating grouped values?
Thanks!
David
David Siegal
Community Software Lab
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
