In this article, we will look into the process of developing functions that returns a table. We will use the film table in the sample database for the demonstration:
The following function returns all films whose titles match a particular pattern using ILIKE operator:
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR) RETURNS TABLE ( film_title VARCHAR, film_release_year INT ) AS $$ BEGIN RETURN QUERY SELECT title, cast( release_year as integer) FROM film WHERE title ILIKE p_pattern ; END; $$ LANGUAGE 'plpgsql';
This get_film(varchar) function accepts one parameter p_pattern which is a pattern that you want to match with the film title.
To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ).
In the function, we return a query that is a result of a SELECT statement. Notice that the columns in the SELECT statement must match with the columns of the table that we want to return. Because the data type of release_yearof the film table is not an integer, we have to convert it into an integer using CAST.
We can test the function using the following statement:
SELECT * FROM get_film ('Al%');
We called the get_film(varchar) function to get all films whose title starts with Al. The following is the result:
Notice that if you call the function using the following statement:
SELECT get_film ('Al%');
PostgreSQL returns a table with one column that holds the array of films.
In practice, you often process each individual row before appending it in the function’s result set. The following example illustrates the idea.
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR, p_year INT) RETURNS TABLE ( film_title VARCHAR, film_release_year INT ) AS $$ DECLARE var_r record; BEGIN FOR var_r IN(SELECT title, release_year FROM film WHERE title ILIKE p_pattern AND release_year = p_year) LOOP film_title := upper(var_r.title) ; film_release_year := var_r.release_year; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql';
We have created a function with the similar name get_film(varchar, int) but accepts two parameters:
- The first parameter is the p_pattern that we used to search for a film if the title matches with this pattern.
- The second parameter is the release year of the film.
The RETURN NEXT statement adds a row to the result set of the function. The execution continues and the result set is building up in each iteration of the loop.
See the following test case:
SELECT * FROM get_film ('%er', 2006);