What is a "non-SETOF function"?

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?

2 Answers

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(); 

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.