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

Advertisements
13 comments
  1. Athailah said:

    Mantap Mr. Yadi, But I have to learn more from you, specially about QT. I very-very interested about this programing.

  2. rudy said:

    Keren…tapi dalam english yaa om..
    tetap semangat…..

  3. Herianto said:

    i wait the next 🙂

  4. Handri said:

    Edun euy Pak Yadi Mah…….

  5. pasman said:

    Cool ! It allows previous releases
    to be compatible with 8.4.

  6. m.dede said:

    come

  7. I love your site!

    _____________________
    Experiencing a slow PC recently? Fix it now!

  8. I noticed that this is not the first time you mention this topic. Why have you decided to write about it again?

  9. Last week I dropped by this site and as usual great content and ideas. Love the lay out and color scheme. Is it new? Well I really really like it. Email me the theme at joanbm3@gmail.com. I love the tips on this site, they are always to the point and just the information I was looking for. Its hard to find good content these days in the world of spam and garbage sites.

  10. *There are certainly a lot of details like that to take into consideration. That is an excellent point to bring up. I offer the thoughts above as general inspiration but clearly you will find questions like the one you bring up where the most critical thing will be working in honest good faith. I don?t know if finest practices have emerged around things like that, but I am sure that your job is clearly identified as a fair game. Both boys and girls really feel the impact of just a moments pleasure, for the rest of their lives.

  11. HanaPipers said:

    🙂

  12. Thanks for the tip, i was using string_agg on my 9.1 but the production server is 8.2. Used string_to_array(array_agg()) to make a query compatible with both, much cleaner than re-activating the obsolete intagg module on 9+ versions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: