Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anybody please summarize the differences between:

http://www.postgresql.org/docs/9.1/static/xfunc-sql.html

and

http://www.postgresql.org/docs/9.1/static/plpgsql.html

?

Main points:

  • conceptional differences
  • given a problem family, convenience of use
  • political issues
share|improve this question
1  
Firstly, SQL (aka. query language) functions do not involve any of PostgreSQL procedural languages. Secondly, you've already found the best source where one can find the answers for your questions (with the exception of the last one - what exactly do youmean by 'political issues'?) –  dezso Sep 6 '12 at 8:46
    
I asked to summarize all the main issues, not necessarily using the documentation that I pasted, but also using personal impressions. Those links are there just to be sure everybody understands what the question is about. –  Gismo Ranas Sep 6 '12 at 9:01
    
Don't take it as an assault, but once you've read those you can summarize it yourself :) Otherwise, your question is a bit broad, but given a bit of time I'll try to collect a few thoughts. –  dezso Sep 6 '12 at 9:07
    
Hallo dezso, no worry, it was just an invitation, I will try to summarize it myself in a few days if nobody does that till then. –  Gismo Ranas Sep 6 '12 at 12:28
1  
Hi cataldo - I'd just like to welcome you to the site as this is your first question here. Thanks for posting and I hope you stick around. –  Jack Douglas Sep 7 '12 at 10:10

2 Answers 2

PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:

  • Use views where possible
  • Where a view is not suitable, use an SQL function
  • Where an SQL function isn't suitable, use PL/PgSQL.
  • Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is
  • ... and where no PL will do the job, use an external program and possibly LISTEN and NOTIFY to talk to it.

Very frequently a view is sufficient when you think a function is needed. Even if it's extremely expensive to SELECT the whole view, WHERE clauses in the query referencing the view are usually pushed down into the view and may result in very different query plans. I've often had big performance improvements from converting SQL functions into views.

The main time you find you can't use a view and should consider an SQL function is when:

  • Parameters that can't be expressed as simple WHERE clauses are needed, like a parameter within a WITH expression
  • You want a security barrier via a SECURITY DEFINER function, and the security_barrier views in PostgreSQL 9.2 and above aren't sufficient for your needs;
  • You need parameters that aren't pushed down into sub-clauses of a view by the optimizer and want to control it more directly; or
  • There are lots of params or there's lots of repetition of the params, so it's impractical to write the query as a view.

For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared STABLE or IMMUTABLE (and not also declared STRICT or SECURITY DEFINER) can also be inlined into the calling statement. That gets rid of the function call overhead and can also sometimes result in huge performance benefits when a WHERE condition in the calling function gets pushed down into the SQL function by the optimizer. Use SQL functions whenever they're sufficient for the task.

The main time SQL functions won't do the job is when you need lots of logic. If/then/else operations that you can't express as CASE statements, lots of re-use of calculated results, building values up from chunks, error handling, etc. PL/PgSQL comes in handy then. Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for:

  • Dynamic SQL and dynamic DDL via the EXECUTE statement
  • When you want to RAISE errors/warnings for the logs or client
  • When you need exception handling - you can trap and handle errors with EXCEPTION blocks instead of having the whole transaction terminate on error
  • Complex conditional logic that doesn't fit CASE ... WHEN very well
  • Lots of re-use of calculated values that you can't do fit into WITH and CTEs
  • Building dynamic records
  • You need to perform an action after producing the result set

With common table expressions (CTEs), especially writable CTEs and WITH RECURSIVE I find I use PL/PgSQL a lot less than I used to because SQL is so much more expressive and powerful. I use views and plain SQL functions a lot more now. It's worth remembering that plain SQL functions can contain more than one statement; the last statement is the function's result.

share|improve this answer
    
Very well said! (an additional albeit virtual +1 for mentioning writable CTEs) –  dezso Sep 7 '12 at 8:32
    
This answers also explains why some functions needs optimization fence. –  Eonil Oct 24 '13 at 6:07

plpgsql is a full-fledged procedural language, with variables, looping constructs, etc. A SQL function is simply a subquery. A SQL function, if it is declared STABLE or IMMUTABLE and not also declared STRICT, can often be inlined into the calling query, as if it were written out on each reference.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.