As part of an intro to databases class, we've been told to create a function using PL/pgSQL that gives out a factorial of a number. Now since x! increases rapidly as a function, we've decided to return a bigint due to it being a large range integer.
CREATE OR REPLACE FUNCTION getBigFactorial(fn bigint) RETURNS bigint as $$
DECLARE
product bigint;
minus1 int;
BEGIN
if (fn > 21) then
RAISE EXCEPTION 'Error: computing factorial; numeric value out of range';
return NULL;
elsif (fn < 1) then
RAISE EXCEPTION 'Error: Argument less than 1!';
return NULL;
end if;
minus1 := fn - 1;
if (minus1 > 0) then
product := fn * getBigFactorial(minus1);
return product;
end if;
return fn;
END;
$$ language plpgsql;
For this function, we've been told to create some sort of validation for the number entered.
The one for (fn < 1) worked flawlessly, however, the one which is (fn > 21) is giving some problems.
When the following is entered:
SELECT getBigFactorial(21);
We get:
ERROR: bigint out of range
CONTEXT: PL/pgSQL function getbigfactorial(integer) line 17 at assignment
SQL state: 22003
rather than getting the desired output. Line 17 corresponds to this line:
if (minus1 > 0) then
When we entered 22 instead of 21, we had the desired error output.
Any help in finding out what causes this? TIA
Your conditional check should be inclusive of 21:
if (fn >= 21) then
-- ...
since for an input of 21, the factorial of 21 (fn * getBigFactorial(minus1), where fn = 21, and minus1 = fn - 1), which is an integer out of range for a bigint, is assigned to product, a bigint.