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

Using a set-returning function

A set-returning function (also known as a table function) can be used in most places a table, view, or subquery can be used. They are a powerful and flexible way to return your data.

You can call the function in the SELECT clause like 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 it in the WHERE clause:

postgres=# SELECT * FROM fibonacci_seq(3) WHERE 1 = ANY(SELECT fibonacci_seq(3));
 fibonacci_seq 
---------------
             0
             1
             1
(3 rows)

Using database side functions for all data access is a great way to ...

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