Re: Aggregate Function with Argument

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