CODEWITHSUNDEEP
sqlpostgresqldatabase-migrationgroup-concatpostgresql-9.4
Jus12
Jus12

Reputation: 18024

Can we define a GROUP_CONCAT function in PostgreSQL?

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.

Upvotes: 0

Views: 5368

Answers (3)

Holger Jakobs
Holger Jakobs

Reputation: 1062

The aggregate function can be modified to work with columns of any type. The result is of type text, though, in this version. It is possible to return the array type of the base type by leaving out the final function.

CREATE OR REPLACE FUNCTION _group_concat(state anyarray, value anyelement)
RETURNS anyarray AS $$
  SELECT CASE
    WHEN value IS NULL THEN state
    WHEN state IS NULL THEN array_append('{}', value)
    ELSE array_append (state, value)
  END
$$ IMMUTABLE LANGUAGE SQL;

CREATE OR REPLACE FUNCTION _group_concat_final(state anyarray) 
RETURNS TEXT AS $$
  SELECT array_to_string (state, ', '::text);
$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE AGGREGATE group_concat (
  BASETYPE = anyelement,
  SFUNC = _group_concat,
  FINALFUNC = _group_concat_final,
  STYPE = anyarray
);

The line WHEN value IS NULL THEN state makes the aggregate function ignore NULL values. If it is left out, NULL values are put into the array. In order to show them, add another argument to the call of array_to_string() with the value you would like to see in the output.

Using array_to_json() instead of array_to_string() is another nice way of creating the final output.

Upvotes: 0

bobflux
bobflux

Reputation: 11581

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 ;)

http://sqlfiddle.com/#!17/c452d/1

Upvotes: 4

Dimitri Fontaine
Dimitri Fontaine

Reputation: 266

Yes this is possible. Have a look at https://github.com/2ndQuadrant/mysqlcompat/blob/master/sql_bits/aggregate.sql where it's already done, as such:

-- GROUP_CONCAT()
-- Note: only supports the comma separator
-- Note: For DISTINCT and ORDER BY a subquery is required
CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
  SELECT CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2
    ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
  END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
    BASETYPE = text,
    SFUNC = _group_concat,
    STYPE = text
);

Upvotes: 2

Related Questions