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

# PL/pgSQL Returns SetOf

**Summary**: in this tutorial, you will learn how to use the `returns setof` option to define a function that returns one or more rows.

## Defining a function with returns setof option

[PostgreSQL functions](https://neon.com/postgresql/postgresql-plpgsql/postgresql-create-function) allow you to encapsulate reusable logic within the database. To return one or more rows from a function, you can use the `returns setof` option.

The `returns setof` option allows you to return one or more rows with a predefined structure from a function.

Here's the syntax for creating a function that returns a set of rows:

```plsql
create or replace function function_name(parameters)
returns setof row_structure
as
$$
   -- logic
   -- ...
   -- return one or more rows
   return query select_query;
$$ language plpgsql;
```

In this syntax:

- First, specify the function name after the `create or replace function` keywords.
- Second, use the `returns setof` with a predefined row structure. The row structure can be a composite type defined in the database.
- Third, return rows inside the function body using the `return query` statement followed by a select statement.

## Calling the function

To call a function with the `returns setof`, you use the following statement:

```plsql
SELECT function_name(argument);
```

It'll return a single column containing an array of all columns of the returned rows.

To retrieve data from a specific column of the return rows, you specify the dot (.) and column name after the function call:

```plsql
SELECT (function_name(argument)).column_name;
```

If you want to retrieve data from all columns of the returned rows, you can use the `.*` like this:

```plsql
SELECT (function_name(argument)).*;
```

Alternatively, you can call the function using the `SELECT...FROM` statement:

```plsql
SELECT * FROM function_name(argument);
```

## PL/pgSQL Returns SetOf example

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

![Film table](https://neon.com/postgresqltutorial/film_table.png)First, define a function that retrieves a film by its id from the `film` table:

```plsql
create or replace function find_film_by_id(
	p_id int
)
returns setof film
as
$$
begin
   return query select * from film
   where film_id = p_id;
end;
$$
language plpgsql;
```

Second, call the `find_film_by_id()` function:

```plsql
SELECT find_film_by_id(100);
```

Output:

```

                                   find_film_by_id

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (100,"Brooklyn Desert","A Beautiful Drama of a Dentist And a Composer who must Battle a Sumo Wrestler in The First Manned Space Station",2006,1,7,4.99,161,21.99,R,"2013-05-26 14:50:58.951",{Commentaries},"'battl':14 'beauti':4 'brooklyn':1 'compos':11 'dentist':8 'desert':2 'drama':5 'first':20 'man':21 'must':13 'space':22 'station':23 'sumo':16 'wrestler':17")
(1 row)
```

The output is an array that contains column data.

Third, retrieve the title of the film with id 100:

```plsql
select (find_film_by_id(100)).title;
```

Output:

```text
      title
-----------------
 Brooklyn Desert
(1 row)
```

Fourth, retrieve the data from all columns of the returned row:

```plsql
SELECT * FROM find_film_by_id(100);
```

Output:

```
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------------------
film_id          | 100
title            | Brooklyn Desert
description      | A Beautiful Drama of a Dentist And a Composer who must Battle a Sumo Wrestler in The First Manned Space Station
release_year     | 2006
language_id      | 1
rental_duration  | 7
rental_rate      | 4.99
length           | 161
replacement_cost | 21.99
rating           | R
last_update      | 2013-05-26 14:50:58.951
special_features | {Commentaries}
fulltext         | 'battl':14 'beauti':4 'brooklyn':1 'compos':11 'dentist':8 'desert':2 'drama':5 'first':20 'man':21 'must':13 'space':22 'station':23 'sumo':16 'wrestler':17
```

Note that we use `\x` command in psql to display the result set vertically.

## Summary

- Use the `returns setof` to return one or more rows from a function.

---

## 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)
- [Function Returns Table](https://neon.com/postgresql/postgresql-plpgsql/plpgsql-function-returns-a-table)
- [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)