SELECT sql_function(...) vs SELECT * FROM sql_function(...)
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.