원본출처 : 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;
|