Difference between language sql and language plpgsql in PostgreSQL functions

Question

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.

Solution 1

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:

  • Re: pl/pgsql functions outperforming sql ones?

• When you need to trap errors.

• For trigger functions.

• When including DDL statements changing objects or altering system catalogs in any way relevant to subsequent commands - because all statements in SQL functions are parsed at once while PL/pgSQL functions plan and execute each statement sequentially (like a prepared statement). See:

  • Why can PL/pgSQL functions have side effect, while SQL functions can't?

Also consider:

• PostgreSQL Stored Procedure Performance

---

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:

• Can I make a plpgsql function return an integer without using a variable?
• The manual on "Returning From a Function"

Solution 2

PL/PgSQL is a PostgreSQL-specific procedural language based on SQL. It has loops, variables, error/exception handling, etc. Not all SQL is valid PL/PgSQL - as you discovered, for example, you can't use SELECT without INTO or RETURN QUERY. PL/PgSQL may also be used in DO blocks for one-shot procedures.

sql functions can only use pure SQL, but they're often more efficient and they're simpler to write because you don't need a BEGIN ... END; block, etc. SQL functions may be inlined, which is not true for PL/PgSQL.

People often use PL/PgSQL where plain SQL would be sufficient, because they're used to thinking procedurally. In most cases when you think you need PL/PgSQL you probably actually don't. Recursive CTEs, lateral queries, etc generally meet most needs.

For more info ... see the manual.

Solution 3

just make the select query you wrote inside the function as the returned value:

 create  or replace function f2(istr  varchar)
 returns text as $$ 
 begin return(select 'hello! '::varchar || istr); end;
 $$ language plpgsql;

Comments

[Erwin Brandstetter]

True, set based solutions are typically superior. But that's no final verdict in whether to use an SQL or a PL/pgSQL function. Newcomers are sometimes using plpgsql for the wrong reasons.

[Craig Ringer]

Well said; I forgot to mention dynamic SQL, too.

[Admin]

Thanks. I guess there is one difference not mentioned in your reply. Is it correct that a SQL function can't contain any command which alter the system catalog, such as CREATE TABLE, while a PL/pgSQL function can? What is the reason? See stackoverflow.com/questions/51004980/…

[Erwin Brandstetter]

@Ben: Not exactly. SQL functions can contain DDL commands. You just need to be aware of implications. And some combinations of commands don't work. I added an answer over there.

[scand1sk]

There is no need for the SELECT statement if this context. RETURN 'hello! '::varchar || istr; is quite enough. The pure SQL version (without begin... return ... end...) is likely to be much more efficient anyway.