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)