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