String Join Aggregate Function in PostgreSQL 8.1

Sometimes I store data in denormalized ways, even in Postgres. This can have performance benefits, and definitely can be faster to develop, easier to understand, and in general, Lower Stress ™. Usually, I tend to use comma-delimited text in those sorts of denormalized fields. In this scenario, it’s useful to have an aggregate function to join such fields. So based on Abulyadi/ and some chatter in freenode#postgresql (vol7ron, xzilla, others), here is an 8.1 idiom for “string join aggregate”:

/* 8.3+? */
CREATE AGGREGATE array_agg(anyelement) (
/* 8.1; the format for CREATE AGGREGATE changes in later versions */
    SFUNC = array_append,
    BASETYPE = anyelement,
    STYPE = anyarray,
    INITCOND = '{}'

once the array_agg is created you can call it as:

SELECT array_to_string(array_agg(some_field), ',') FROM some_table;

One Comment on “String Join Aggregate Function in PostgreSQL 8.1”

  1. sswam says:

    thanks, this is just what I need 🙂

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s