I have several lines of SQL code for my legacy database with GROUP_CONCAT statements, as in:
SELECT SUM(age), GROUP_CONCAT(sal) FROM Users;
In PostgreSQL, I can do the same with:
SELECT SUM(age), string_agg(sal, ', ') FROM Users;
I would like to reuse the old SQL as much as possible. So I need to define a GROUP_CONCAT function that internally calls string_agg. Is this possible?
EDIT: The linked question is unrelated!
My question asks “How to define a function called group_concat?”. The linked question says “How can we do something equivalent to group concat (without defining a new function)?”. None of the answers, also match what I want.
The selected answer is spot on! Thankfully it was answered before getting closed.
Advertisement
Answer
There is a string_agg() builtin which does what you want, but you specifically ask for it to be named group_concat for MySQL compatibility. Unfortunately, string_agg() uses an internal data type for accumulation (presumably to avoid copying the whole buffer on each append, I have not looked at the source though) and I didn’t find a way to declare a SQL aggrerate identical to string_agg().
Defining group_concat() function would not work either, as pg has to be made aware that it is an aggregate, not a function with an aggregate hidden inside, which would not work. Such a function would operate on one row at a time: any aggregate inside would just aggregate a single row and return it unchanged…
Thus, this code will accumulate the elements into an array, then add the “,” delimiters with array_to_string. I will use the array_agg() declaration (before it became a built-in) as a model, and simply add a finalizer function which will convert the aggregated array into text.
CREATE OR REPLACE FUNCTION _group_concat_finalize(anyarray)
RETURNS text AS $$
SELECT array_to_string($1,',')
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE group_concat(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FFUNC=_group_concat_finalize,
INITCOND='{}'
);
SELECT group_concat(x) FROM foo;
The nice thing is that it should work fine for any type, without hassle, thanks to the generic types “anyarray” and “anyelement”.
I would presume this would be slower than string_agg() if string_agg does indeed avoid to copy the whole aggregation array on each append. This should matter only if the number of rows to be grouped into each set is large, though. In this case, you probably can spend a minute editing the SQL query 😉