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. > > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings