How to create aggregate varchar with pg function

postgresql 允許使用者自訂Group Function，這是Oracle作不到的，
這裏就一個很實用的aggregate function，把table欄位的字串聯接起來成一筆資料

--先建function
CREATE OR REPLACE FUNCTION dbaegis_concate(text, text)
RETURNS text
LANGUAGE plpgsql
AS
$body$
DECLARE
t text;
BEGIN
IF character_length($1) > 0 AND character_length($2) > 0 THEN
t := $1||','||$2;
ELSIF character_length($1) = 0 AND character_length($2) > 0 THEN
t := ''||$2;
ELSIF character_length($1) > 0 AND character_length($2) = 0 THEN
t := $1;
ELSE t := '';
END IF;

RETURN t;
END;
$body$
VOLATILE
COST 100;
/


--再建aggregate type
CREATE AGGREGATE dbaegis_aggregate_concate(text)
(
sfunc = dbaegis_concate,
stype = text
);
/