Question 89581 in PostgreSQL Question posted in
The official documentation can be found here.

Generating Fibonacci sequence with PL/pgSQL function – Postgresql

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);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    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;
    RETURN NEXT;

    fibonacci_num := 1;
    RETURN NEXT;
    
    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);

  2. 0

    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$;

    fiddle

    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".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search