How to Return Values from Functions in PL/pgSQL

When you're writing functions in PostgreSQL, one of the first things you gotta figure out is how to return a result. Sometimes you just need to return a single number. Sometimes—a whole table. And sometimes—even multiple data sets. In this section, we'll go through all the main options: from the simplest RETURN to RETURN QUERY, RETURNS TABLE, and SETOF.

One Result: RETURN

If your function just needs to return a single value—like a sum or a row count—just use a regular RETURN.

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

When you create a function in PL/pgSQL, you gotta specify what it returns. This is what the RETURNS keyword is for—it defines the "format" of the result your function spits out. So if you wanna return a single number, some text, or a table of data—you gotta put that in the RETURNS line.

Here's a simple example:

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Here, the RETURNS INT bit tells you the function returns a number.

Returning a Single Value

Let's start with the simplest thing—a function that returns just one value. For example, a function that counts the number of students in the students table:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Save the query result into the total variable
    RETURN total; -- Return the result
END;
$$ LANGUAGE plpgsql;

Now we can call this function:

SELECT count_students(); -- Will return the number of students

Returning Multiple Values with RETURNS TABLE

Sometimes you need to return not just one value, but a whole set of records. For example, a list of all students with their names and IDs. For that, you use the RETURNS TABLE construct.

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students; -- Return the query result as a table
END;
$$ LANGUAGE plpgsql;

Now we can call this function:

SELECT * FROM get_students(); -- Will return a table with all students

Notice the RETURNS TABLE keywords. They tell you the function returns a table with the specified columns (id and name in this case).

Using RETURN QUERY

You probably already liked our example above. But here's another detail: RETURN QUERY is like a magic wand in PL/pgSQL that lets you return data straight from a query. With it, you can return the result of a whole query or just a subset.

Let's say we need to return all students who are actively studying (their status in the database is marked as active = TRUE):

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE active = TRUE; -- Return only active students
END;
$$ LANGUAGE plpgsql;

Now we can call the function and get data about active students:

SELECT * FROM get_active_students();

Returning Multiple Rows without RETURNS TABLE

In some cases, you might want to return rows of data without using RETURNS TABLE. For that, you can use the SETOF data type. This lets you return rows of data with the same structure. For example:

CREATE FUNCTION get_student_names() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN QUERY
    SELECT name
    FROM students;
END;
$$ LANGUAGE plpgsql;

This function just returns a list of student names:

SELECT * FROM get_student_names();

Returning Values Depending on Input Data

Functions don't always just return static results. They can use parameters to change up the results dynamically.

Here's an example of returning data by student ID

CREATE FUNCTION get_student_by_id(student_id INT) RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE id = student_id; -- Use the student_id parameter
END;
$$ LANGUAGE plpgsql;

Now you can request info about a specific student:

SELECT * FROM get_student_by_id(3); -- Will return data for the student with ID = 3

Returning Complex Data (Multiple Sets)

Sometimes the data is so complex you gotta return it in multiple sets. For that, you can use cursors. For example, if you wanna provide two data sets from a function—a list of active students and a list of inactive students.

CREATE FUNCTION get_students_status() RETURNS SETOF RECORD AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, 'active' AS status
    FROM students
    WHERE active = TRUE;

    RETURN QUERY
    SELECT id, name, 'inactive' AS status
    FROM students
    WHERE active = FALSE;
END;
$$ LANGUAGE plpgsql;

Now you can get both data sets:

SELECT * FROM get_students_status();

Common Mistakes When Working with RETURNS

Not specifying a return type: If you don't say what the function returns, PostgreSQL will throw an error. For example:

CREATE FUNCTION no_return_type() AS $$ -- Error, RETURNS not specified
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Mismatched data types: Make sure the values you return match the declared types. For example, if you said you're returning INT, don't try to return a string.

Forgetting to use RETURN QUERY: If you forget to use RETURN QUERY for a complex query, the function just won't return anything.

Incorrectly returning multiple values: If you're returning a row of data but forgot to use SETOF or TABLE, PostgreSQL will throw an error.