Difference between language sql and language plpgsql in PostgreSQL functions

user3814846 picture user3814846 · Jul 15, 2014 · Viewed 35.3k times · Source

Am very new in Database development so I have some doubts regarding my following example:

Function f1() - language sql

 create or replace function f1(istr  varchar) returns text as $$ 
 select 'hello! '::varchar || istr;
 $$ language sql;

Function f2() - language plpgsql

 create  or replace function f2(istr  varchar)
 returns text as $$ 
 begin select 'hello! '::varchar || istr; end;
 $$ language plpgsql;
  • Both functions can be called like select f1('world') or select f2('world').

  • If I call select f1('world') the output will be:

    `hello! world`
    
  • And output for select f2('world'):

    ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function f11(character varying) line 2 at SQL statement ****** Error ******

  • I wish to know the difference and in which situations I should use language sql or language plpgsql.

Any useful link or answers regarding functions will much appreciated.

Answer

Erwin Brandstetter picture Erwin Brandstetter · Jul 16, 2014

SQL functions

are the better choice:

  • For simple scalar queries. Not much to plan, better save any overhead.

  • For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below.

  • If they are typically called in the context of bigger queries and are simple enough to be inlined.

  • For lack of experience with any procedural language like PL/pgSQL. Many know SQL well and that's about all you need for SQL functions. Few can say the same about PL/pgSQL. (Though it's rather simple.)

  • A bit shorter code. No block overhead.

PL/pgSQL functions

are the better choice:

  • When you need any procedural elements or variables that are not available in SQL functions, obviously.

  • For any kind of dynamic SQL, where you build and EXECUTE statements dynamically. Special care is needed to avoid SQL injection. More details:

  • Postgres functions vs prepared queries

  • When you have computations that can be reused in several places and a CTE can't be stretched for the purpose. In an SQL function you don't have variables and would be forced to compute repeatedly or write to a table. This related answer on dba.SE has side-by-side code examples for solving the same problem using an SQL function / a plpgsql function / a query with CTEs:

  • How to pass a parameter into a function

Assignments are somewhat more expensive than in other procedural languages. Adapt a programming style that doesn't use more assignments than necessary.

  • When a function cannot be inlined and is called repeatedly. Unlike with SQL functions, query plans can be cached for all SQL statements inside a PL/pgSQL functions; they are treated like prepared statements, the plan is cached for repeated calls within the same session (if Postgres expects the cached (generic) plan to perform better than re-planning every time. That's the reason why PL/pgSQL functions are typically faster after the first couple of calls in such cases.

Here is a thread on pgsql-performance discussing some of these items:

Also consider:


To actually return from a PL/pgSQL function, you could write:

CREATE FUNCTION f2(istr varchar)
  RETURNS text AS
$func$
BEGIN
   RETURN 'hello! ';  -- defaults to type text anyway
END
$func$ LANGUAGE plpgsql;

There are other ways: