array_agg for PostgreSQL version < 8.4

In relation with article by Hubert Lubaczewski ‘Waiting for 8.4 – array aggregate and array unpacker’, I have the need to use the former feature for one of my project.

I have my aggregate function as follows:

CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND=’{}’
);

The sample of table definition looks like:

CREATE TABLE tes
(
id integer NOT NULL,
grp integer NOT NULL,
label text NOT NULL,
CONSTRAINT tes_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);

Populate table so it contains records:

id grp     label
1      1       ABC
2      1      DEF
3      2      GHI
4      2      JKL

Run query:
SELECT grp, array_agg(label) FROM tes GROUP BY grp;
Result:
grp array_agg
1 {ABC,DEF}
2 {GHI,JKL}

The array at the second column then -of course- can be converted to string:
SELECT grp,array_to_string( array_agg(label), ‘,’ ) FROM tes GROUP BY grp;
Result:
grp array_to_string
1 ABC,DEF
2 GHI,JKL