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) (
    SFUNC=array_append,
    STYPE=anyarray,
    INITCOND=’{}’
);

/* 8.1; the format for CREATE AGGREGATE changes in later versions */
CREATE AGGREGATE array_agg (
    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;