CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Vianney Morain
Vianney Morain

Reputation: 123

custom aggregate function in postgres return NULL value

in order to create a more complex custom aggregate function, i followed first this amazing tutorial.

Here the data i use :

create table entries(
  id serial primary key,
  amount float8 not null
);

select setseed(0);

insert into entries(amount)
select (2000 * random()) - 1000
from generate_series(1, 1000000);

So I have this table :

 id      |        amount         |   running_total
---------+-----------------------+--------------------
       1 |     -462.016298435628 |  -462.016298435628
       2 |      162.440904416144 |  -299.575394019485
       3 |     -820.292402990162 |  -1119.86779700965
       4 |     -866.230697371066 |  -1986.09849438071
       5 |      -495.30001822859 |   -2481.3985126093
       6 |      772.393747232854 |  -1709.00476537645
       7 |     -323.866365477443 |  -2032.87113085389
       8 |     -856.917716562748 |  -2889.78884741664
       9 |      285.323366522789 |  -2604.46548089385
      10 |     -867.916810326278 |  -3472.38229122013
-- snip --

And I would like the max of the running_total column
(I know I can do do it without a new aggregate function, but it's for the demonstration)

So i've made this aggregate function

create or replace function grt_sfunc(agg_state point, el float8)
returns point
immutable
language plpgsql
as $$
declare
  greatest_sum float8;
  current_sum float8;
begin
    current_sum := agg_state[0] + el;
    greatest_sum := 40;
    /*if agg_state[1] < current_sum then
        greatest_sum := current_sum;
    else
        greatest_sum := agg_state[1];
    end if;*/
    return point(current_sum, greatest_sum);
    /*return point(3.14159, 0);*/
end;
$$;

create or replace function grt_finalfunc(agg_state point)
returns float8
immutable
strict
language plpgsql
as $$
begin
  return agg_state[0];
end;
$$;

create or replace aggregate greatest_running_total (float8)
(
    sfunc = grt_sfunc,
    stype = point,
    finalfunc = grt_finalfunc
);

Normally it sould work, but in the end, it gives me a null result :

select greatest_running_total(amount order by id asc)
from entries;


id    |  running_total
---------+---------------
    1 |  [NULL]

I tried to change the type of the data, to check the 2 first aggregate functions separately, they are working well. Does someone could help me find a solution please ? :)

Thank you very much !

Upvotes: 0

Views: 643

Answers (2)

Vianney Morain
Vianney Morain

Reputation: 123

So, the solution was to add an initial condition. Indeed, without initial condition, the first value is considered as NULL :D (thank you @jjanes and @The Impaler)

So I corrected ma aggregated function :

create or replace aggregate greatest_running_total (float8)
(
    sfunc = grt_sfunc,
    stype = point,
    finalfunc = grt_finalfunc,
    initcond = '(0,0)'
);

And, indeed SQL indexes its tables from 1 and not from 0... Here was my second mistake,

Thank you very much !!

Upvotes: 0

jjanes
jjanes

Reputation: 44137

You need to set a non-NULL initcond for the aggregate. Presumably that would be (0,0), or maybe negative very large numbers for each? Or manually check for the agg_state being NULL.

Also, it seems like your grt_finalfunc should be returning subscript [1], not [0].

Upvotes: 3

Related Questions