PostgreSQL Tutorial: PL/pgSQL Function Returns A Table

Summary: in this tutorial, you will learn how to develop PostgreSQL functions that return a table.

To define a function that returns a table, you use the following form of the create function statement:

create or replace function function_name (
   parameter_list
) 
returns table ( column_list ) 
language plpgsql
as $$
declare 
-- variable declaration
begin
-- body
end; $$ 

Instead of returning a single value, this syntax allows you to return a table with a specified column list:

returns table ( column_list )

We will use the film table from 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
	) 
	language plpgsql
as $$
begin
	return query 
		select
			title,
			release_year::integer
		from
			film
		where
			title ilike p_pattern;
end;$$

This get_film(varchar) accepts one parameter p_pattern which is a pattern that you want to match with the film title.

The function returns a query that is the result of a select statement. Note that the columns in the result set must be the same as the columns in the table defined after the returns table clause.

Because the data type of release_year column from the film table is not integer, you need to cast it to an integer using the cast operator ::.

The folowing shows how to call the get_film() function:

SELECT * FROM get_film ('Al%');

Output:

If you call the function using the following statement, PostgreSQL returns a table that consists of one column that holds an array of rows:

SELECT get_film ('Al%');

Output:

In practice, you often process each individual row before appending it in the function’s result set:

create or replace function get_film (
	p_pattern varchar,
	p_year int
) 
returns table (
	film_title varchar,
	film_release_year int
) 
language plpgsql
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; $$ 

In this example, we created the get_film(varchar,int) that accepts two parameters:

• The p_pattern is used to search for films.
• The p_year is the release year of the films.

In the function body, we used a for loop staetment to process the query row by row.

The return next statement adds a row to the returned table of the function.

The following illustrates how to call the get_film() function:

SELECT * FROM get_film ('%er', 2006);

Note that this example is for the demonstration purposes.

Summary

Use the returns table (column_list) in the create function to define a function that returns a table (or result set).

See more

PostgreSQL PL/pgSQL Tutorial