Skip site navigation (1) Skip section navigation (2)

Re: Aggregate Function with Argument

From: David Siegal <dsiegal(at)brave(dot)cs(dot)uml(dot)edu>
To: Mark Gibson <gibsonm(at)cromwell(dot)co(dot)uk>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Aggregate Function with Argument
Date: 2004-10-21 15:26:16
Message-ID: Pine.LNX.4.58.0410211124480.22849@brave.cs.uml.edu (view raw or flat)
Thread:
Lists: pgsql-sql
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.
>
>

In response to

pgsql-sql by date

Next:From: Kent AndersonDate: 2004-10-21 18:58:52
Subject: Finding duplicated values
Previous:From: Achilleus MantziosDate: 2004-10-21 12:35:11
Subject: Re: Offtopic: psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group