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

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