Aggregate Function with Argument

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

Re: Aggregate Function with Argument

On Mon, Oct 18, 2004 at 11:24:33 -0400,
David Siegal wrote:
>
> 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?

This exact same question has been asked previously and should be in
the archives. My memory was that there currently isn't a way to pass
the delimiter as an argument, but I don't remember whether or not
any work arounds were suggested.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Aggregate Function with Argument

David Siegal wrote:
> 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.

I've managed to do this in two stages:

1. Collect the set of values into an array.
This can be done using a custom aggregate function, array_accum,
which is demonstrated within the PostgreSQL manual:
http://www.postgresql.org/docs/7.4/interactive/xaggr.html

But here it is again:

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

It makes me wonder why this isn't a built-in aggregate???

2. Convert the array to a string.
Using the built-in function array_to_string:
http://www.postgresql.org/docs/7.4/interactive/functions-arr ay.html

Example:

SELECT
team_number,
array_to_string(array_accum(member_name), ', ') AS members
FROM team
GROUP BY team_number;


You can also go full round-trip (delimited string -> set) using the
builtin function: string_to_array, and a custom pl/pgSQL function:

CREATE FUNCTION array_enum(anyarray) RETURNS SETOF anyelement AS '
DECLARE
array_a ALIAS FOR $1;
subscript_v integer;
BEGIN
FOR subscript_v IN array_lower(array_a,1) .. array_upper(array_a,1)
LOOP
RETURN NEXT array_a[subscript_v];
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql'
STRICT IMMUTABLE;

Example:

SELECT * FROM array_enum(string_to_array('one,two,three',','));

--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org