PostgreSQL Performance – SQL Function with SELECT vs. PLPGSQL Function with RETURN QUERY SELECT

functionsperformanceplpgsqlpostgresqlpostgresql-performance

Is there a difference between a plain SQL function doing a SQL query:

create function get_sports() returns setof sport as
$body$
    select * from sport;
$body$
language sql stable;

and PLPGSQL function returning that same SQL query:

create function get_sports() returns setof sport as
$body$
begin
    return query select * from sport;
end
$body$
language plpgsql stable;

with regards to performance? In which situation should which version be used?

And what if we pass arguments, does that change anything? Such as:

create function get_sports(status int) returns setof sport as
$body$
    select * from sport where status = $1;
$body$
language sql stable;

create function get_sports(status int) returns setof sport as
$body$
begin
    return query select * from sport where status = $1;
end
$body$
language plpgsql stable;

Best Answer

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 SETOFsometype, 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 UNION (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).

Related:

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:

Related Question