cr_bucket_using_width_bucket.sql

Save this script as cr_bucket_using_width_bucket.sql.

-- This approach subtracts a tiny value, epsilon, from the input value
-- to change "width_bucket()"'s "closed-open" interval bucket semantics to
-- the required "open-closed" interval bucket semantics.
--
-- You might consider this to be a rather obscure and possibly risky trick.
-- However, this implementation of "bucket()" does pass the rigorous
-- acceptance test.

create or replace function bucket(
  val          in double precision,
  lower_bound  in double precision default 0,
  upper_bound  in double precision default 1,
  no_of_values in int              default 10)
  returns int
  language plpgsql
as $body$
begin
  declare
    one     constant int              := 1;
    zero    constant double precision := 0;
    epsilon constant double precision := 0.0000000001;

    result constant int not null :=
      case
        when val between zero and epsilon
          then one
        else
          width_bucket((val - epsilon), lower_bound, upper_bound, no_of_values)
      end;
  begin
    assert
      (result between one and no_of_values),
    'bucket():'||
    ' val '||val||
    ' must be between lower_bound '||lower_bound||
    ' and upper_bound '||upper_bound;

    return result;
  end;
end;
$body$;