SELECT sql_function(...) vs SELECT * FROM sql_function(...)

Question

I'm reading the PostgreSQL documentation for the first time and when facing SQL functions I think I have understood all the basics, but still can't see the difference between SELECT sql_function(...) and SELECT * FROM sql_function(...) and when to use one or the other.

Answer 1

You typically use a function in FROM if it is a set-returning function (SRF). Such functions return a result set rather than a single value or tuple.

But, confusingly, this is not written in stone in PostgreSQL:

• You can also call a SRF in the SELECT list, as in SELECT f().

  As the documentation says:

  Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.

• You can use any function in the FROM clause. It is then treated as a SRF that returns only a single row.

Answer 2

The former is used on a scalar function (i.e. it returns a scalar result / single value), the latter is used on a table function (a function that returns a result set of values as opposed to a single value), which is why the syntax is to select from it as opposed to the former.

Solomon Rutzky does a beautiful job describing the differences between a Scalar and Table function in his answer to this StackOverflow post. (It's Microsoft SQL Server specific but the concepts are the same, especially for your high level question in this context.)