up vote 8 down vote favorite
5

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.

share|improve this question
4  
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 – Clodoaldo Neto Jul 15 '14 at 10:20
1  
@ClodoaldoNeto someone may lazy to find out or not able to find out the correct solutions...!! – user3814846 Jul 15 '14 at 10:23
1  
@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. – Igor Romanchenko Jul 15 '14 at 10:29
    
@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. – Peter Krauss Mar 16 '16 at 11:06
up vote 13 down vote accepted

SQL functions

are the better choice:

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

  • For single calls per session. Nothing to gain from plan caching and 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.

  • 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 call in such cases.
    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).

Also consider:

share|improve this answer
    
Well said; I forgot to mention dynamic SQL, too. – Craig Ringer Jul 16 '14 at 4:00
    
About performance, only "SQL vs PLpgSQL" performance, there are a link with nowadays consolidate information (rules and tips)? Not a forum, but a "fast guide"... Example of need: there are some "compile directive" to say that a SQL function must be cached (query plan)? – Peter Krauss Mar 16 '16 at 10:07
    
Not sure I understand your question. Either way, please ask your question as question. Comments are not the place. You can always reference this post for context in your question and/or post a comment here with a link to your related question. – Erwin Brandstetter Mar 16 '16 at 16:45
up vote 7 down vote

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.

share|improve this answer
    
well explained this is what i expected – user3814846 Jul 15 '14 at 10:45
1  
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. – Erwin Brandstetter Jul 16 '14 at 3:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.