How to Develop a PL/pgSQL Function That Returns a Table
Summary: in this tutorial, you will learn how to develop PostgreSQL functions that return a table using PL/pgSQL.
To define a function that returns a table, you use the following form of the create function statement:
Instead of returning a single value, this syntax allows you to return a table with a specified 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 the ILIKE operator.
This get_film(varchar)
accepts one parameter p_pattern
, which is a pattern you want to match with the film title.
The function returns a query set based on a select statement. You need to ensure that the columns in the result set match those you define in the table after the returns table
clause.
Since the data type of release_year
column from the film
table is not an integer, you need to cast it to an integer using the cast operator ::
.
The following shows how to call the get_film()
function:
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:
Output:
In practice, you often process each row before appending it to the function’s result set:
In this example, we create 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 use a for
loop statement 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:
Output:
Summary
- Use the
returns table (column_list)
in thecreate function
to define a function that returns a table (or result set).