I'm trying to generate the Fibonacci sequence with a function in SQL. It takes an input parameter pstop integer
(10 by default) and returns a table with all Fibonacci numbers less than pstop
.
But the output starts with these numbers (1, 2, 3, 5, 8, 13) and skips the numbers of the beginning of the sequence which are 0 and 1 (0, 1, 2, 3, 5, 8, 13).
How can I fix it?
CREATE OR REPLACE FUNCTION fnc_fibonacci (pstop INTEGER DEFAULT 10)
RETURNS TABLE (fibonacci_num INTEGER) AS $BODY$
DECLARE
a INTEGER;
b INTEGER;
c INTEGER;
BEGIN
a := 0;
b := 1;
fibonacci_num := 0;
WHILE
fibonacci_num < pstop LOOP
c := a + b;
fibonacci_num := c;
IF
fibonacci_num < pstop THEN
RETURN NEXT;
END IF;
a := b;
b := c;
END LOOP;
END;
$BODY$
LANGUAGE PLPGSQL;
SELECT * FROM fnc_fibonacci(20);
To note: the Fibonacci sequence starts with 0, 1, 1, 2 (not 0, 1, 2).
Assignments are ever so slightly expensive in PL/pgSQL. So keep those at a minimum. It's an academic consideration for a function returning no more than 46 rows with type integer
. (It gets more relevant with big numbers operating with numeric
.)
Anyway, here is an optimized function with a single addition and assignment per output row:
CREATE OR REPLACE FUNCTION f_fibonacci (pstop int = 10)
RETURNS SETOF int
LANGUAGE plpgsql IMMUTABLE STRICT AS
$func$
DECLARE
a int := 0;
b int := 1;
BEGIN
/*
-- optional sanity check:
-- function starts operating at 2
-- and int4 computation overflows past Fibonacci Nr. 1836311903
IF pstop NOT BETWEEN 2 AND 1836311903 THEN
RAISE EXCEPTION 'Pass integer betwen 2 and 1836311903. Received %', pstop;
END IF;
*/
-- main
RETURN NEXT 0;
RETURN NEXT 1;
LOOP
a := a + b;
EXIT WHEN a >= pstop;
RETURN NEXT a;
b := b + a;
EXIT WHEN b >= pstop;
RETURN NEXT b;
END LOOP;
END;
$func$;
Note that we can simply RETURN NEXT 0;
in a function declared with RETURNS SETOF int
without naming any OUT
parameters. (Column names in RETURNS TABLE
are OUT
parameters, too.)
Details in the manual chapter "Returning from a Function".