Обсуждение: 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