Database Reference
In-Depth Information
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 re-
turn just the
n
th number, but the whole sequence of numbers up to the
n
th 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 func-
tion is defined as returning a
SETOF
integer.
