3

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;
3

There are various subtle differences.

Simple SQL functions can be inligned 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 like prepared statements - only planning each when executed the first time, and 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.

Passing arguments works pretty much the same for either PL. (There used to be more differences in old verions, like SQL functions couldn't refer to parameter names. But that's history.)

There are other more or less subtle differences. See:

  • This was very informational, thank you Erwin. – Katrin Sep 15 '18 at 10:57

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.