Functions returning SETOF in PostgreSQL

Introduction

Functions returning SETOF let you get multiple rows as a result, like a small table. This helps when you want to reuse queries that return many rows.

You want to create a reusable query that returns a list of items.

You need to return multiple rows from a function instead of just one value.

You want to simplify complex queries by wrapping them in a function.

You want to filter or process data and return all matching rows.

You want to return rows from a table with some custom logic.

Syntax

PostgreSQL

CREATE FUNCTION function_name(parameters) RETURNS SETOF return_type AS $$
BEGIN
  RETURN QUERY
  SELECT ...;
END;
$$ LANGUAGE plpgsql;

The keyword SETOF means the function returns multiple rows of the specified type.

Use RETURN QUERY to return the rows from a SELECT statement inside the function.

Examples

This function returns all rows from the users table.

PostgreSQL

CREATE FUNCTION get_all_users() RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY SELECT * FROM users;
END;
$$ LANGUAGE plpgsql;

This function returns users who are at least min_age years old.

PostgreSQL

CREATE FUNCTION get_users_by_age(min_age INT) RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY SELECT * FROM users WHERE age >= min_age;
END;
$$ LANGUAGE plpgsql;

Sample Program

This example creates a table employees and inserts some rows. Then it defines a function that returns all employees in a given department. Finally, it calls the function to get employees in the 'Sales' department.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
);

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'HR'),
('Charlie', 'Sales');

CREATE FUNCTION get_employees_by_department(dept TEXT) RETURNS SETOF employees AS $$
BEGIN
  RETURN QUERY SELECT * FROM employees WHERE department = dept;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employees_by_department('Sales');

Important Notes

Functions returning SETOF are useful to return multiple rows like a table.

You can call these functions in the FROM clause like a table.

Remember to use RETURN QUERY inside the function to return rows.

Summary

Functions with SETOF return multiple rows, not just one value.

Use them to wrap queries that return many rows for reuse and clarity.

Call these functions in SELECT * FROM function_name() to get the rows.