Postgres aggregate alias for group_concat using string_agg

Question

I know that postgres does not have group_concat, but I want to emulate it for strings by using string_agg (or any other way that works).

I need to have the function called group_concat because of an inability to change legacy code.

How can I do this?

For what its worth, I also tried implementing group_concat using regular concat, and ran into an error there as well:

CREATE AGGREGATE group_concat (text) (sfunc = concat, stype=text)

error:

"function concat(text,text) does not exist"

Answer 1

-- drop aggregate if exists group_concat(text);
CREATE AGGREGATE group_concat(text) (
  SFUNC=textcat,
  STYPE=text
);

select group_concat(x) from unnest('{a,b,c,d}'::text[]) as x;

textcat is the function used internally by the || operator:

CREATE OPERATOR ||(
  PROCEDURE = textcat,
  LEFTARG = text,
  RIGHTARG = text);

---

Update

To make the comma as the separator:

--drop aggregate if exists group_concat(text);
--drop function if exists group_concat_trans(text, text);

create or replace function group_concat_trans(text, text)
  returns text
  language sql
  stable as 
$$select concat($1,case when $1 is not null and $2 is not null then ',' end,$2)$$;

create aggregate group_concat(text) (
  sfunc=group_concat_trans,
  stype=text);

select group_concat(x) from unnest(array['a','b','c',null,'d']) as x;

╔══════════════╗
║ group_concat ║
╠══════════════╣
║ a,b,c,d      ║
╚══════════════╝