From: Mark Gibson <gibsonm@cromwell.co.uk>
To: David Siegal <dsiegal@thecsl.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate Function with Argument
Date: Tue, 19 Oct 2004 15:50:08 +0100
Message-ID: <895d38be5cdba0588f326681d5508329417529a6@cromwell.co.uk> (raw)
In-Reply-To: <Pine.LNX.4.58.0410181117500.29842@brave.cs.uml.edu>
References: <Pine.LNX.4.58.0410181117500.29842@brave.cs.uml.edu>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gibsonm@cromwell.co.uk, dsiegal@thecsl.org
Subject: Re: Aggregate Function with Argument
In-Reply-To: <895d38be5cdba0588f326681d5508329417529a6@cromwell.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox