Summary: in this tutorial, we will show you how to develop PostgreSQL functions that return 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 integer, we have to convert it into integer using type cast.
We can test the function using the following statement.
1 2 3 4 | 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:
1 2 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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_patternthat we use to search for film if the title matches with this pattern. We use the ILIKE operator to perform the search. - The second parameter is the release year of the film.
By the way, this is called an overloaded function in PostgreSQL.
Because we want to process each individual row before returning it, we use FOR LOOP statement to process it. Inside each iteration, we use UPPER function to make the title of the film upper case. This operation is for the demonstration purpose only.
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:
1 2 3 4 | SELECT * FROM get_film ('%er', 2006); |
Now you should understand and know how to develop a function that returns a table using RETURN QUERY and RETURN NEXT statements.