Thread: aggregate functions
Hi there, is there any easy way to add the string concatenation operator as a collating operator? I would like to do something like: id val ------ 1 aa 1 bb 2 cc 2 dd 2 ee select id,join(val,',') from table group by id id join ----------- 1 aa,bb 2 cc,dd,ee and it would be nice not having to revert to DBI :-) Regards, Mit freundlichem Gruß, Holger Klawitter -- Holger Klawitter +49 (0)251 484 0637 holger@klawitter.de http://www.klawitter.de/
> is there any easy way to add the string concatenation operator > as a collating operator? I would like to do something like: Blimey, I should have looked for "create aggregate". But to those interested here is my solution. create function joinfunc( text, text ) returns text as ' begin if $1 = '''' then return $2; else return ( $1 || '', '' ) || $2; end if; end; ' language 'plpgsql'; create aggregate join ( basetype = text, sfunc1 = joinfunc, stype1 = text, initcond1 = '' ); Perhaps someone want to put that into the documentation. By the way, there is still one problem: select join(col) group by ... does not work, one has to type select "join"(col) group by ... I assume this is bug ... Regards, Holger Klawitter -- Holger Klawitter +49 (0)251 484 0637 holger@klawitter.de http://www.klawitter.de/
On Mon, 19 Jun 2000, Holger Klawitter wrote: > Perhaps someone want to put that into the documentation. > By the way, there is still one problem: > > select join(col) group by ... > does not work, one has to type > select "join"(col) group by ... > > I assume this is bug ... JOIN is a reserved word in 7.0, to implement ANSI SQL JOINs -alex