[Firebird-devel] User-defined aggregate functions

[Adriano dos Santos Fernandes]

Hi!

I propose creation of user-defined aggregate functions.

Lets initially define the requirements for it:
- It should work with empty data set too, in case of use without GROUP
BY clause: i.e., COUNT results in 0, while most others functions result
in NULL
- It should also produce "running (or accumulated) aggregation", like
SUM(...) OVER (ORDER BY ...)

What is not proposed here:
- Make a single function work with different source/target data types.
Custom aggregate function is statically typed as normal functions.

Design is open if good alternatives are presented. Initially I think:

- There should be one input parameter and one output parameter.
- SUSPEND is used to make engine feed the input parameter with a new
value, setting AGG_FINISHED to TRUE/FALSE, and returning running
(accumulated) aggregation value.
- Empty data set makes the function already starts with AGG_FINISHED =
TRUE, and non empty data set makes the function starts with AGG_FINISHED
= FALSE and the first row already fed in the input parameter
- When AGG_FINISHED is TRUE, it's expected that user code does not call
SUSPEND anymore, so only for the empty data set case, the value present
in the output parameter when the function ends is the result of the
aggregate function in the query.

Below I put some example (tested mentally only) functions:


------------------
-- Works as standard SUM.
create aggregate function custom_sum (i integer) returns (o integer)
as
begin
    while (not agg_finished)
    do
    begin
        if (i is not null) then
        begin
            if (o is null) then
                o = 0;

            o = o + i;
        end

        suspend;
    end
end


-- Works as standard AVG.
create aggregate function custom_avg (i double precision) returns (o
double precision)
as
    declare count integer = 0;
    declare accumulated double precision = 0;
begin
    while (not agg_finished)
    do
    begin
        if (i is not null) then
        begin
            count = count + 1;
            accumulated = accumulated + i;
            o = accumulated / count;
        end

        suspend;
    end
end


-- Works as standard COUNT.
 create aggregate function custom_count (i integer) returns (o integer)
as
begin
    o = 0;

    while (not agg_finished)
    do
    begin
        if (i is not null) then
            o = o + 1;
        suspend;
    end
end


-- This function shows the difference of returning value in SUSPEND when
data set is not empty and returning in function termination when data
set is empty.
-- select custom_count_plus_1000(1) from rdb$database -- returns 1
-- select custom_count_plus_1000(1) from rdb$database where 1 = 0 --
returns 1000
create aggregate function custom_count_plus_1000 (i integer) returns (o
integer)
as
begin
    o = 0;

    while (not agg_finished)
    do
    begin
        o = o + 1;
        suspend;
    end

    o = o + 1000;
end
------------------


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel