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.


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.

  • Any known drawback with calling a SFR with SELECT or an escalar function with SELECT * FROM?
    – Héctor
    Dec 10 '20 at 8:31
  • The first can lead to unexpected behavior if you have more than one table function in the select list, but other than that, no. Dec 10 '20 at 8:36

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.)

  • 1
    note the question is in regards to postgresql which has different implementations than sql server in this area. Some examples (link) Dec 9 '20 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.