PostgreSQL数据库中group_concat函数的定义

group_concat函数的自定义

CREATE OR REPLACE FUNCTION group_concat_agg(text, anyelement,text)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2::text
    ELSE $1 || $3 || $2::text
  END;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE AGGREGATE group_concat(anyelement,text) (
  SFUNC = group_concat_agg,
  STYPE = text
);


CREATE OR REPLACE FUNCTION group_concat_agg(text, anyelement)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN $2 IS NULL THEN $1
    WHEN $1 IS NULL THEN $2::text
    ELSE $1 || ',' || $2::text
  END;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE AGGREGATE group_concat(anyelement) (
  SFUNC = group_concat_agg,
  STYPE = text
);

-- CREATE OR REPLACE FUNCTION group_concat_agg(text, anyelement,text,anyelement)
-- RETURNS text AS $$
-- BEGIN
--   RETURN CASE
--     WHEN ($2 IS NULL) or ($4 is null) THEN $1
--     WHEN $1 IS NULL THEN $2::text || $3 || $4::text
--     ELSE $1 || ',' || $2::text || $3 || $4::text
--   END;
-- END;
-- $$ IMMUTABLE LANGUAGE plpgsql;

-- CREATE AGGREGATE group_concat(anyelement,text,anyelement) (
--   SFUNC = group_concat_agg,
--   STYPE = text
-- );

CREATE OR REPLACE FUNCTION group_concat_agg(text, text,text,bigint)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN ($2 IS NULL) or ($4 is null) THEN $1
    WHEN $1 IS NULL THEN $2::text || $3 || $4::text
    ELSE $1 || ',' || $2::text || $3 || $4::text
  END;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE AGGREGATE group_concat(text,text,bigint) (
  SFUNC = group_concat_agg,
  STYPE = text
);

测试

select "id","type" from t_rule;
select * from t_resource;

select "type",group_concat("id",',') from t_rule group by "type";
select group_concat("id",',') from t_rule where "type" = 'SWITCH';
select group_concat("id") from t_rule where "type" = 'SQL';
select group_concat("id") from t_rule;

select "type",group_concat("id",',') from t_resource group by "type";

select "type",group_concat(concat(TO_CHAR(created_at,'YYYY-MM-DD'),'_',"id"),',') from t_resource group by "type";
select "type",group_concat(TO_CHAR(created_at,'YYYY-MM-DD'),'_',"id") from t_resource group by "type";

select concat(TO_CHAR(created_at,'YYYY-MM-DD'),'_',"id") from t_resource;