Thread: Concatenate aggregate?

I'd like to concatenate text from one field that has been returned by a
grouped query.

e.g.

create table test (id int, thetext text);
CREATE
playj=> insert into test values (1, 'Hello number 1');
INSERT 697997 1
playj=> insert into test values (2, 'Hello number 2');
INSERT 697998 1
playj=> insert into test values (1, 'Hello again number 1');
INSERT 697999 1
playj=> select * from test;
id |       thetext
----+---------------------- 1 | Hello number 1 2 | Hello number 2 1 | Hello again number 1
(3 rows)

I can do:

playj=> select id, max(thetext) from test group by id;
id |      max
----+---------------- 1 | Hello number 1 2 | Hello number 2
(2 rows)

But what I'd like to do is something like:

playj=> select id, concat(thetext, ' -- ') from test group by id;
id |      concat
----+---------------- 1 | Hello number 1 -- Hello again number 1 2 | Hello number 2
(2 rows)

[Ordering is unimportant here.]

Any pointers to useful starting points please?

Thanks

Julian Scarfe

One way is to create your own aggregate function. See the reference manual
under CREATE AGGREGATE.

On Tue, 6 Aug 2002, Julian Scarfe wrote:

> I'd like to concatenate text from one field that has been returned by a
> grouped query.
>
> e.g.
>
> create table test (id int, thetext text);
> CREATE
> playj=> insert into test values (1, 'Hello number 1');
> INSERT 697997 1
> playj=> insert into test values (2, 'Hello number 2');
> INSERT 697998 1
> playj=> insert into test values (1, 'Hello again number 1');
> INSERT 697999 1
> playj=> select * from test;
>
>  id |       thetext
> ----+----------------------
>   1 | Hello number 1
>   2 | Hello number 2
>   1 | Hello again number 1
> (3 rows)
>
> I can do:
>
> playj=> select id, max(thetext) from test group by id;
>
>  id |      max
> ----+----------------
>   1 | Hello number 1
>   2 | Hello number 2
> (2 rows)
>
> But what I'd like to do is something like:
>
> playj=> select id, concat(thetext, ' -- ') from test group by id;
>
>  id |      concat
> ----+----------------
>   1 | Hello number 1 -- Hello again number 1
>   2 | Hello number 2
> (2 rows)
>
> [Ordering is unimportant here.]
>
> Any pointers to useful starting points please?
>
> Thanks
>
> Julian Scarfe
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

From: "Archibald Zimonyi" <archie@netg.se>

> One way is to create your own aggregate function. See the reference manual
> under CREATE AGGREGATE.

Thank you.

http://www.postgresql.org/idocs/index.php?sql-createaggregate.html

And indeed Chris's LIST() aggregate does almost exactly what I want.  I
don't think it was there last time I tried to figure out CREATE AGGREGATE.
The examples on that page are very helpful.

Julian