Returning sets

When you write a set returning function, there are some differences from a normal scalar function. Let's first take a look at returning a set of integers.

Returning a set of integers

We will revisit our Fibonacci number generating function, but this time we will not return just the nth number, but the whole sequence of numbers up to the nth number.

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 as returning a SETOF integer.

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