Forum OpenACS Q&A: fun with aggregates: sybase ASE can't do this, but postgres can

For the rare occasions when you need to denormalize a relationship into a CDL, here's an easy way to do it in PG (courtesy of Chris Rohlfs in the idocs):

DROP FUNCTION comma_cat(text, text);
CREATE FUNCTION comma_cat(text, text) RETURNS text AS
'select case
WHEN $2 is null or $2 = '''' THEN $1
WHEN $1 is null or $1 = '''' THEN $2
ELSE $1 || '', '' || $2
END'
LANGUAGE 'sql';

DROP aggregate list text;
create aggregate list(
    sfunc1=comma_cat,
    basetype=text,
    stype1=text,
    initcond1=''
);

now you can do queries like

select p.id, list(m.id::text)
from parties p left join minions m on (p.id = m.party_id)
group by p.id;

with ease!

(and yes, I know list is built-in to sybase ASA, and you could conceivably write this in ASE with the expensive Java add-on... but PG can do this out of the box, and the $thousands ASE doesn't. which has caused me large amounts of grief at my day job.)