> This page location: PostgreSQL PL/pgSQL > Function Returns Table
> Full Neon documentation index: https://neon.com/docs/llms.txt

# 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](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-function) statement:

```plsql
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:

```plsql
returns table ( column_list )
```

We will use the `film` table from the [sample database](../postgresql-getting-started/postgresql-sample-database) for the demonstration:

![](https://neon.com/postgresqltutorial/film.png)The following function returns all films whose titles match a particular pattern using the [ILIKE operator](../postgresql-tutorial/postgresql-like).

```plsql
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 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](../postgresql-tutorial/postgresql-data-types) of `release_year` column from the `film` table is not an integer, you need to cast it to an integer using the [cast operator](../postgresql-tutorial/postgresql-cast) `::`.

The following shows how to call the `get_film()` function:

```plsql
SELECT * FROM get_film ('Al%');
```

Output:

```text
    film_title    | film_release_year
------------------+-------------------
 Alabama Devil    |              2006
 Aladdin Calendar |              2006
 Alamo Videotape  |              2006
 Alaska Phantom   |              2006
 Ali Forever      |              2006
 Alice Fantasia   |              2006
 Alien Center     |              2006
 Alley Evolution  |              2006
 Alone Trip       |              2006
 Alter Victory    |              2006
(10 rows)
```

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

```plsql
SELECT get_film ('Al%');
```

Output:

```
         get_film
---------------------------
 ("Alabama Devil",2006)
 ("Aladdin Calendar",2006)
 ("Alamo Videotape",2006)
 ("Alaska Phantom",2006)
 ("Ali Forever",2006)
 ("Alice Fantasia",2006)
 ("Alien Center",2006)
 ("Alley Evolution",2006)
 ("Alone Trip",2006)
 ("Alter Victory",2006)
(10 rows)
```

In practice, you often process each row before appending it to the function's result set:

```plsql
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 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:

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

Output:

```
         film_title          | film_release_year
-----------------------------+-------------------
 ACE GOLDFINGER              |              2006
 ALI FOREVER                 |              2006
 ALIEN CENTER                |              2006
 AMISTAD MIDSUMMER           |              2006
 ARACHNOPHOBIA ROLLERCOASTER |              2006
 DYING MAKER                 |              2006
 BIRDCAGE CASPER             |              2006
...
```

## Summary

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

---

## Related docs (PostgreSQL PL/pgSQL)

- [Introduction](https://neon.com/postgresql/postgresql-plpgsql/introduction-to-postgresql-stored-procedures)
- [Block Structure](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-block-structure)
- [Variables](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-variables)
- [Constants](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-constants)
- [IF-ELSE Statements](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-if-else-statements)
- [CASE Statement](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-case-statement)
- [Loop Statements](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-loop-statements)
- [FOR Loop](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-for-loop)
- [WHILE Loop](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-while-loop)
- [EXIT Statement](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-exit)
- [CONTINUE Statement](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-continue)
- [Cursor](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-cursor)
- [Function Parameters](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-parameters)
- [Function Overloading](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-overloading)
- [Returns SETOF](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-returns-setof)
- [Record Types](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-record-types)
- [Row Types](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-row-types)
- [Error Messages](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-errors-messages)
- [Exception Handling](https://neon.com/postgresql/postgresql-plpgsql/postgresql-exception)
- [CREATE FUNCTION](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-function)
- [CREATE PROCEDURE](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-procedure)
- [DROP FUNCTION](https://neon.com/postgresql/postgresql-plpgsql/postgresql-drop-function)
- [DROP PROCEDURE](https://neon.com/postgresql/postgresql-plpgsql/postgresql-drop-procedure)
- [Stored Procedure with INOUT Parameters](https://neon.com/postgresql/postgresql-plpgsql/postgresql-stored-procedure-with-inout-parameters)
- [Introduction to Stored Procedures](https://neon.com/postgresql/postgresql-plpgsql/introduction-to-postgresql-stored-procedures)
- [SELECT INTO](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-select-into)
- [ASSERT Statement](https://neon.com/postgresql/postgresql-plpgsql/pl-pgsql-assert)
- [Dollar-Quoted String Constants](https://neon.com/postgresql/postgresql-plpgsql/dollar-quoted-string-constants)