Aggregate Function with Argument - Mailing list pgsql-sql

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