Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is what I want 9 times out of 10. With a non-aggregate function, I could simply do this:

But since string_agg is an aggregate function, this doesn't work. It complains that the field I'm passing to it needs to be in the GROUP BY clause, which tells me that the query engine doesn't understand that my wrapper is an aggregate function.

I looked at the docs for user-defined aggregates (http://www.postgresql.org/docs/9.1/static/xaggr.html and http://www.postgresql.org/docs/9.1/static/sql-createaggregate.html) but it's not obvious to me how I could define my new string_agg(text) using the existing string_agg(text, text). It seems I would have to define my new function from scratch, which is more complexity than I want to take on, and I'm also not entirely confident I could mirror the built-in string_agg's behavior perfectly.

So is there some technique for creating a simple wrapper than I'm not seeing? Or am I going to have to Do More Work?

1 Answer
1

For all I know, you cannot wrap an aggregate function. You have to write your own aggregate function.

If you write your own function, you may run into incompatibilies when upgrading or porting to another database. So, personally, I wouldn't bother and just add the delimiter to string_agg() on every call. It really isn't that much of a pain.