What is a "non-SETOF function"?

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?

Solution

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

Comments

[lucastamoios]

Just to add, returning a row would also make a function "non-SETOF".