---连接以关键字分组的字符串
---首先创建一个状态传递函数:实现将统计结果传递连接
create function pg_concat( text, text ) returns text as
$BODY$
begin
if $1 is null then
return $2;
else
return $1 || $2;
end if;
end; $BODY$ language 'plpgsql';
create function pg_concat( text, text ) returns text as
$BODY$
begin
if $1 is null then
return $2;
else
return $1 || $2;
end if;
end; $BODY$ language 'plpgsql';
--创建一个最终的结果函数:实现将最后一个连接符号清除
create function pg_concat_fin(text) returns text as
$$
begin
return substr($1,1,length($1)-1);
end;
$$ language 'plpgsql';
create function pg_concat_fin(text) returns text as
$$
begin
return substr($1,1,length($1)-1);
end;
$$ language 'plpgsql';
--创建聚集函数,入口参数basetype:text,状态传递函数:pg_concat,状态变量类型:text,结果函数finalfunc:pg_concat_fin
create aggregate pg_concat (
basetype = text,
sfunc = pg_concat,
stype = text,
finalfunc = pg_concat_fin);
create aggregate pg_concat (
basetype = text,
sfunc = pg_concat,
stype = text,
finalfunc = pg_concat_fin);
--使用聚集函数进行统计:
select id,pg_concat(name||',') from test group by id;
select id,pg_concat(name||',') from test group by id;