By user3814846


2014-07-15 10:10:41 8 Comments

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.

3 comments

@Erwin Brandstetter 2014-07-16 03:06:03

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:
    Re: pl/pgsql functions outperforming sql ones?

  • When you need to trap errors.

  • For trigger procedures (which are just functions, too).

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

@Craig Ringer 2014-07-16 04:00:49

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

@user3284469 2018-06-25 04:16:17

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 2018-06-26 02:12:54

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

@ZORRO_BLANCO 2018-11-14 14:36:27

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;

@Craig Ringer 2014-07-15 10:31:13

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.

@user3814846 2014-07-15 10:45:54

well explained this is what i expected

@Erwin Brandstetter 2014-07-16 03:10:45

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.

Related Questions

Sponsored Content

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

11 Answered Questions

3 Answered Questions

[SOLVED] Convert INT to VARCHAR SQL

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

3 Answered Questions

1 Answered Questions

[SOLVED] Why iam getting this error while executing Postgres function

  • 2019-02-25 07:21:39
  • Ramu Vishwakarma
  • 37 View
  • -1 Score
  • 1 Answer
  • Tags:   postgresql plpgsql

20 Answered Questions

[SOLVED] What is the difference between call and apply?

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

31 Answered Questions

[SOLVED] What's the difference between a method and a function?

24 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1205791 View
  • 1268 Score
  • 24 Answer
  • Tags:   sql union union-all

Sponsored Content