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 *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 function is defined ...

Start Free Trial

No credit card required