A set returning function (also known as a table function) can be used in most places where a table, view, or subquery can be used. They are a powerful and flexible way to return data.
You can call the function in the SELECT clause, as you do with a scalar function:
postgres=# SELECT fibonacci_seq(3);
fibonacci_seq
---------------
0
1
1
(3 rows)You can also call the function as part of the FROM clause:
postgres=# SELECT * FROM fibonacci_seq(3);
fibonacci_seq
---------------
0
1
1
(3 rows)You can even call the function in the WHERE clause:
postgres=# SELECT * FROM fibonacci_seq(3) WHERE 1 = ANY(SELECT fibonacci_seq(3));
fibonacci_seq
---------------
0
1
1
(3 rows)You can limit the result set, just as in the case of querying a table:
postgres=# SELECT * FROM fibonacci_seq(10) as fib WHERE fib > 3;
fibonacci_seq
---------------
5
8
13
21
34
(5 rows)