Database Reference
In-Depth Information
Using database side functions for all data access is a great way to secure your ap-
plication, help with performance, and allow for easy maintenance. Table functions al-
low 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 would have been available.
Returning rows from a function
It would often be very helpful to return back to the client even more information than
a set of integers. You may need all of 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.
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 in-
tegers, we use
pg_language
which is a table.
You could also have used
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 ]-+----------