August 4, 2023
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).