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