How to Return SETOF from Function in PostgreSQL: Syntax and Example

In PostgreSQL, to return multiple rows from a function, use RETURNS SETOF followed by the row type or table type. Define the function with LANGUAGE plpgsql and use RETURN QUERY or RETURN NEXT to output rows.

Syntax

The basic syntax to create a function returning multiple rows uses RETURNS SETOF followed by a table name or a composite type. Inside the function, you can use RETURN QUERY to return a set of rows from a query.

• RETURNS SETOF type_name: Specifies the function returns multiple rows of the given type.
• RETURN QUERY: Executes a query and returns its result set.
• LANGUAGE plpgsql: Defines the function language.

sql

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

Example

This example creates a function that returns all rows from the employees table. It demonstrates how to use RETURNS SETOF and RETURN QUERY to return multiple rows.

sql

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

INSERT INTO employees (name, department) VALUES
('Alice', 'HR'),
('Bob', 'IT'),
('Carol', 'Finance');

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

-- Call the function
SELECT * FROM get_all_employees();

Output

 id | name  | department 
----+-------+------------
  1 | Alice | HR         
  2 | Bob   | IT         
  3 | Carol | Finance    
(3 rows)

Common Pitfalls

Common mistakes when returning SETOF include:

• Not specifying RETURNS SETOF and using RETURNS type instead, which returns only one row.
• Forgetting to use RETURN QUERY or RETURN NEXT inside the function body.
• Using RETURN alone, which returns a single value, not a set.

Always ensure the function's return type matches the query output.

sql

/* Wrong: returns only one row */
CREATE FUNCTION wrong_func() RETURNS employees AS $$
BEGIN
  RETURN SELECT * FROM employees LIMIT 1;
END;
$$ LANGUAGE plpgsql;

/* Right: returns multiple rows */
CREATE FUNCTION right_func() RETURNS SETOF employees AS $$
BEGIN
  RETURN QUERY SELECT * FROM employees;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Keyword	Description
RETURNS SETOF type_name	Function returns multiple rows of the specified type
RETURN QUERY	Returns the result set of a query from the function
RETURN NEXT	Returns one row at a time inside a loop
LANGUAGE plpgsql	Defines the procedural language used for the function

Key Takeaways

Use RETURNS SETOF with a table or composite type to return multiple rows.

Inside the function, use RETURN QUERY to return a set of rows from a SELECT statement.

Always match the function's return type with the query's output structure.

Avoid using RETURN alone when you want to return multiple rows.

Use LANGUAGE plpgsql for procedural functions returning SETOF.