Postgresql – Is it possible to wrap aggregate functions in Postgres

aggregatefunctionspostgresql

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?

Best Answer

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.

Related Question