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