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 using string_agg (or any other way that works).

I need to have a group_concat function due to the inability to change the legacy code.

How can i do this?

For what it's worth, I also tried to implement group_concatusing a regular concat, and ran into an error:

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

error:

"concat function (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;

textcatis a function used inside an operator ||:

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

---

Update

Make a comma as a 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      ║
╚══════════════╝