String Join Aggregate Function in PostgreSQL 8.1
Posted: February 24, 2010 Filed under: db | Tags: posgres, postgresql 1 CommentSometimes 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;