Book Image

PostgreSQL Server Programming

Book Image

PostgreSQL Server Programming

Overview of this book

Table of Contents (21 chapters)
PostgreSQL Server Programming Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Using a set returning function


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)

Using database-side functions...