Take the 2-minute tour ×
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:

create or replace function public.string_agg(text)
returns text
as $$
    select string_agg($1, ', ');
$$ language sql
immutable;

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

That said, it's not that complicated to write your own aggregate function, either:

A custom final function:

CREATE FUNCTION f_str_agg_final(anyarray)
    RETURNS text LANGUAGE SQL AS
$func$SELECT array_to_string($1, ', ')$func$;

The aggregate:

CREATE AGGREGATE f_str_agg (anyelement) (
    SFUNC     = array_append
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
   ,FINALFUNC = f_str_agg_final
);

Call:

SELECT f_str_agg (kat) from tbl;

Works for almost any type - except for array types. You would have to modify your aggregate for this. More in this closely related answer on SO.

share|improve this answer
    
the major reasons I have done this in the past is if I am supporting older versions which don't have the same aggregates. –  Chris Travers Sep 30 '12 at 3:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.