pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregate Function with Argument
4+ messages / 4 participants
[nested] [flat]
* Aggregate Function with Argument
@ 2004-10-18 15:24 David Siegal <dsiegal@thecsl.org>
0 siblings, 3 replies; 4+ messages in thread
From: David Siegal @ 2004-10-18 15:24 UTC (permalink / raw)
To: pgsql-sql
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.
For example:
With a table, 'team'...
team_number member_name
--------------------------
1 David
1 Sanjay
1 Marie
2 Josh
2 Rani
...
...a query like:
SELECT team_number, aggregated_concat(member_name, ', ' ) AS members FROM
team GROUP BY team_number;
...would return:
team_number members
-----------------------------------
1 David, Sanjay, Marie
2 Josh, Rani
...
Here's what I've got so far:
/* For the default case, with no delimiter provided: */
CREATE FUNCTION concat(text, text) RETURNS text
AS 'select $1 || $2;'
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;
/* With a delimiter provided: */
CREATE FUNCTION concat(text, text, text) RETURNS text
AS 'select $1 || $3 || $2;'
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE aggregated_concat (
sfunc = concat,
basetype = text,
stype = text
);
My problem is I don't see how to make aggregated_concat accept an
optional delimiter argument.
Maybe it's not possible?
Any ideas?
Is there some completely different approach I should consider for
concatenating grouped values?
Thanks!
David
David Siegal
Community Software Lab
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate Function with Argument
@ 2004-10-18 16:05 Bruno Wolff III <bruno@wolff.to>
parent: David Siegal <dsiegal@thecsl.org>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2004-10-18 16:05 UTC (permalink / raw)
To: David Siegal <dsiegal@thecsl.org>; +Cc: pgsql-sql
On Mon, Oct 18, 2004 at 11:24:33 -0400,
David Siegal <dsiegal@thecsl.org> wrote:
>
> My problem is I don't see how to make aggregated_concat accept an
> optional delimiter argument.
> Maybe it's not possible?
>
> Any ideas?
> Is there some completely different approach I should consider for
> concatenating grouped values?
This exact same question has been asked previously and should be in
the archives. My memory was that there currently isn't a way to pass
the delimiter as an argument, but I don't remember whether or not
any work arounds were suggested.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate Function with Argument
@ 2004-10-19 14:50 Mark Gibson <gibsonm@cromwell.co.uk>
parent: David Siegal <dsiegal@thecsl.org>
2 siblings, 0 replies; 4+ messages in thread
From: Mark Gibson @ 2004-10-19 14:50 UTC (permalink / raw)
To: David Siegal <dsiegal@thecsl.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Aggregate Function with Argument
@ 2004-10-21 15:26 David Siegal <dsiegal@brave.cs.uml.edu>
parent: David Siegal <dsiegal@thecsl.org>
2 siblings, 0 replies; 4+ messages in thread
From: David Siegal @ 2004-10-21 15:26 UTC (permalink / raw)
To: Mark Gibson <gibsonm@cromwell.co.uk>; +Cc: 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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-10-21 15:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-18 15:24 Aggregate Function with Argument David Siegal <dsiegal@thecsl.org>
2004-10-18 16:05 ` Bruno Wolff III <bruno@wolff.to>
2004-10-19 14:50 ` Mark Gibson <gibsonm@cromwell.co.uk>
2004-10-21 15:26 ` David Siegal <dsiegal@brave.cs.uml.edu>
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