원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=139

Submitted on: 04-12-2001

Description:

I use this to denormalize a list into comma-separated values, rather than having to write a separate query to show related records. (Of course, you could also do this w/semicolons, newlines, '

' tags, etc., given your problem. See a longer explanation of these technique at http://www.zope.org/Members/pupq)

Code:

-- creat function to comma-ify a list

create function comma_aggregate(text,text) returns text as '

begin

if (length($1) > 0 ) then

return $1 || ', ' || $2;

else

return $2;

end if;

end;

' language 'plpgsql';

-- create the aggregate function

create aggregate comma (basetype=text, sfunc=comma_aggregate, stype=text, initcond='' );

-- test it out

select fname, lname, comma(hobbies) from people join hobbies on (personid) group by personid, fname, lname;