PostgreSQL Server Programming - Second Edition

Returning sets

When you write a set returning function, there are some differences from a normal scalar function. First, let's take a look at how to return a set of integers.

Returning a set of integers

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