ERROR: cannot use RETURN NEXT in a non-SETOF function

Encountering the error "ERROR: cannot use RETURN NEXT in a non-SETOF function" when defining a PostgreSQL function might leave you puzzled. Let's delve into the issue and explore solutions to resolve it.

The error occurs when you attempt to use RETURN NEXT within a function that's not declared as a SETOF function. RETURN NEXT is designed to be used in conjunction with SETOF functions, which return a set of values rather than a single value.

To rectify this error, you can either modify the function to return a set of values or refrain from using RETURN NEXT altogether.

Solution 1: Return a Set of Values

To return a set of values from your function, declare the return type as SETOF data_type. For instance, if you want your function to return a set of records, you would declare the return type as SETOF RECORD.

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS
$$
DECLARE
  row RECORD;
BEGIN
  FOR row IN VALUES ('John', 'Smith'), ('David', 'Miller') LOOP
    RETURN NEXT row;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Now, when you call the my_func() function, it will return a set of records that can be iterated over using a FOREACH loop or utilized in a FROM clause of a query.

postgres=# SELECT * FROM my_func() AS (f_n TEXT, l_n TEXT);
 f_n  |  l_n
-------+--------
 John  | Smith
 David | Miller
(2 rows)

Solution 2: Avoid Using RETURN NEXT

If you don't need to return a set of values from your function, you can simply omit the RETURN NEXT statement and instead return a single value using the RETURN statement.

CREATE FUNCTION my_func() RETURNS TEXT AS
$$
DECLARE
  name TEXT;
BEGIN
  FOR name IN VALUES ('John Smith'), ('David Miller') LOOP
    RETURN name;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Now, when you call the my_func() function, it will return a single value, which can be assigned to a variable or used directly in a query.

postgres=# SELECT my_func();
 my_func
---------
 John Smith
(1 row)

These solutions should help you resolve the "ERROR: cannot use RETURN NEXT in a non-SETOF function" error and enable you to define PostgreSQL functions that return the desired results.