O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required