105

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.

Improve this question
4
  • 10
    any usefull link or answers regarding functions will much appreciated Who would ever think about the manual? postgresql.org/docs/current/static/server-programming.html Commented Jul 15, 2014 at 10:20
  • 2
    @ClodoaldoNeto someone may lazy to find out or not able to find out the correct solutions...!! Commented Jul 15, 2014 at 10:23
  • 2
    @keet You may want to read this chapter of the manual: postgresql.org/docs/current/static/plpgsql.html It contains full description of PL/pgSQL language with examples. Commented Jul 15, 2014 at 10:29
  • 1
    @ClodoaldoNeto PostgreSQL-Guide was reviewd many times, is a good text; but there are no "open stakeholder curation" and no crowdsourcing mechanism to enhance it. So: PostgreSQL is bad (!) for many users and many kinds of use. Commented Mar 16, 2016 at 11:06

3 Answers 3

Reset to default
144

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:

  • 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:

    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:

  • 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:

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Well said; I forgot to mention dynamic SQL, too.
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/…
@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.
25

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.

Improve this answer

1 Comment

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.
1

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;
Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.