Open In App

PostgreSQL – Function Returning A Table

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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);

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads