We will revisit our Fibonacci number generating function; however, this time we will not just return the nth number, but the whole sequence of numbers up to the nth number, as shown here:

CREATE OR REPLACE FUNCTION fibonacci_seq(num integer)
  RETURNS SETOF integer AS $$
DECLARE
  a int := 0;
  b int := 1;
BEGIN
  IF (num <= 0)
    THEN RETURN;
  END IF;

  RETURN NEXT a;
  LOOP
    EXIT WHEN num <= 1;
    RETURN NEXT b;

      num = num - 1;
      SELECT b, a + b INTO a, b;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

The first difference we see, is that instead of returning a single integer value, this function is defined to return a SETOF integer.

Then, if you examine the code carefully, you will see that there are two different types of RETURN statements. The first is the ordinary RETURN function in the following code snippet:

IF (num <= 0)
    THEN RETURN;

In this case, the IF function is used to terminate the fibonacci_seq function early, if the length of the desired sequence of Fibonacci numbers is zero or less.

The second kind of RETURN statement is used to return values and continue execution:

RETURN NEXT a;

RETURN NEXT appends rows to the result set of the function, and the execution continues until a normal RETURN statement is encountered or until the control reaches the end of the function. You may have noticed that there are a few other things we did differently in this Fibonacci example, than we did earlier. First, we declared and initialized the variables a and b inside the DECLARE section, instead of first declaring and then initializing them. We also used the argument as a down counter instead of using a separate variable to count from zero and then comparing it with the argument.

Let's test our function now. In the next section, we will discuss how to use the set returning functions in more detail:

postgres=# SELECT fibonacci_seq(5);
 fibonacci_seq 
---------------
      0
      1
      1
      2
      3
 (5 rows)

Both of these techniques save a few lines of code and can make the code more readable, depending on your preferences. However, the longer versions might be easier to follow and understand, so we don't particularly endorse either way.