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 for all the data access is a great way to secure your application; it also helps with performance and allows easy maintenance. Table functions allow you to use functions in all cases where you would have been forced to use more complex queries from the client if only scalar functions were available.
Returning rows from a function will often be helpful to return back to the client more information than just a set of integers. You may need all the columns from an existing table, and the simplest way to declare a return type for a function is to just use the table as part of the return definition, as shown here:
CREATE OR REPLACE FUNCTION installed_languages()
RETURNS SETOF pg_language AS $$
BEGIN
RETURN QUERY SELECT * FROM pg_language;
END;
$$ LANGUAGE plpgsql;Notice that you still need the SETOF part, but instead of defining it as a set of integers, we use pg_language, which is a table.
You can use
TYPE, defined using the CREATE TYPE command or even VIEW:
hannu=# SELECT * FROM installed_languages(); -[ RECORD 1 ]-+---------- lanname | internal lanowner | 10 lanispl | f lanpltrusted | f lanplcallfoid | 0 laninline | 0 lanvalidator | 2246 lanacl | -[ RECORD 2 ]-+---------- lanname | c lanowner | 10 lanispl | f lanpltrusted | f lanplcallfoid | 0 laninline | 0 lanvalidator | 2247 lanacl | -[ RECORD 3 ]-+---------- lanname | sql lanowner | 10 lanispl | f lanpltrusted | t lanplcallfoid | 0 laninline | 0 lanvalidator | 2248 lanacl | -[ RECORD 4 ]-+---------- lanname | plpgsql lanowner | 10 lanispl | t lanpltrusted | t lanplcallfoid | 12596 laninline | 12597 lanvalidator | 12598 lanacl | -[ RECORD 5 ]-+---------- lanname | plpythonu lanowner | 10 lanispl | t lanpltrusted | f lanplcallfoid | 17563 laninline | 17564 lanvalidator | 17565 lanacl |