Re: 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

On Mon, Oct 18, 2004 at 11:24:33 -0400,
David Siegal <dsiegal(at)thecsl(dot)org> 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.

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-array.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 <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Mark,
Works beautifully. Thanks for the clear explanation and code!
-David

On Tue, 19 Oct 2004, Mark Gibson wrote:

> 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-array.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 <gibsonm |AT| cromwell |DOT| co |DOT| uk>
> Web Developer & Database Admin
> Cromwell Tools Ltd.
> Leicester, England.
>
>