What is a "non-SETOF function" in PostgreSQL?

Question

Getting into PL/pgSQL…

I'm still learning the syntax. Sometimes, I'd run into this error message:

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

This sounds sort of cryptic and I could not find information in the Postgres documentation. Hence the question:

• What's a non-SETOF function?

And likewise, assuming there's such a thing, what's a SETOF function?

Answer 1

What's a non-SETOF function?

It's a function returning a single (scalar) value, e.g. an integer or a varchar, e.g.

select upper('foo')` 

upper() is a "non-SETOF" function, it only returns a single value. So a function that is e.g. defined as returns integer can't return the complete result of a SELECT query, it needs to use return 42; but it can't use return query ...;

---

what's a SETOF function?

It's a function that returns a result set - similar to a table (it's typically declared as returns table (...). And you can use it like a table:

select *
from some_set_returning_function();

Answer 2

As the documentation says:

an SQL function can be declared to return a set (that is, multiple rows) by specifying the function's return type as SETOF sometype, or equivalently by declaring it as RETURNS TABLE(columns). In this case all rows of the last query's result are returned.

For instance, if it returns a row or a scalar it would be a non-SETOF.

Answer 3

I got the same error below:

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

When I tried to create my_func() which has RECORD type in a RETURNS clause and RETURN QUERY statement as shown below:

CREATE FUNCTION my_func() RETURNS RECORD AS $$
BEGIN                          -- ↑ ↑ ↑
  RETURN QUERY VALUES ('John', 'Smith'), ('David', 'Miller') LOOP
END;    
$$ LANGUAGE plpgsql;

*Memos:

• I also got and solved the same kind of error with a RETURN NEXT statement.

So, I set SETOF RECORD type to the RETURNS clause as shown below, then I could create my_func() without error:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
BEGIN                          -- ↑ ↑ ↑ ↑ ↑ ↑
  RETURN QUERY VALUES ('John', 'Smith'), ('David', 'Miller');
END;    
$$ LANGUAGE plpgsql;

Then, I could call my_func() in a FROM clause without error as shown below:

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

Or, I set TABLE() type to the RETURNS clause as shown below:

                               -- ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
BEGIN
  RETURN QUERY VALUES ('John', 'Smith'), ('David', 'Miller');
END;    
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

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

postgres=# SELECT my_func();
    my_func
----------------
 (John,Smith)
 (David,Miller)
(2 rows)