X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3D68D32A0DC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Oct 2004 15:50:24 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 19373-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Oct 2004 14:50:11 +0000 (GMT)
Received: from ariel.cromwell.co.uk (mx0.cromwell.co.uk [213.249.208.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 7004C32A0E1
	for <pgsql-sql@postgresql.org>; Tue, 19 Oct 2004 15:50:13 +0100 (BST)
Message-ID: <895d38be5cdba0588f326681d5508329417529a6@cromwell.co.uk>
Date: Tue, 19 Oct 2004 15:50:08 +0100
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: David Siegal <dsiegal@thecsl.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate Function with Argument
References: <Pine.LNX.4.58.0410181117500.29842@brave.cs.uml.edu>
In-Reply-To: <Pine.LNX.4.58.0410181117500.29842@brave.cs.uml.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/125
X-Sequence-Number: 19228

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.