There are various subtle differences.
Simple SQL functions can be inlined when nested in an outer query - provided they meet some preconditions. Not possible for a PL/pgSQL function.
All statements in the same SQL function are planned at once at call time, while PL/pgSQL function treat nested SQL DML statements much like prepared statements - only planning each when executed the first time, and possibly saving the query plan for the duration of a session. This is a major difference that influences behavior and performance.
For example, you cannot create a (temporary) table and use it later in the same SQL function, because it would have to exist at call time for the later statements to be planned. You need PL/pgSQL for that.
Code branches in PL/pgSQL functions that are not reached and thus never planned or executed may harbor undetected bugs until finally executed. PL/pgSQL function are only tested superficially at creation time.
Also, an SQL function only returns the output of the last query. The manual:
When an SQL function is declared as returning
sometype, the function's final query is executed to completion, and each row it outputs is returned as an element of the result set.
Bold emphasis mine. (You might use
ALL) to combine multiple query results.)
RETURN QUERY, on the other hand, appends to the result and can be called multiple times. The manual:
RETURN NEXT and
RETURN QUERY do not actually return from the function
— they simply append zero or more rows to the function's result set.
Execution then continues with the next statement in the PL/pgSQL
function. As successive
RETURN NEXT or
RETURN QUERY commands are
executed, the result set is built up. A final
RETURN, which should
have no argument, causes control to exit the function (or you can just
let control reach the end of the function).
Passing arguments works pretty much the same for either PL. (There used to be more differences in old versions, like SQL functions couldn't refer to parameter names. But that's history.)
There are other, more or less subtle differences. See: