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
Best Answer
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:
LISTEN
andNOTIFY
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:
WHERE
clauses are needed, like a parameter within aWITH
expressionSECURITY DEFINER
function, and thesecurity_barrier
views in PostgreSQL 9.2 and above aren't sufficient for your needs;For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared
STABLE
orIMMUTABLE
(and not also declaredSTRICT
orSECURITY 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:EXECUTE
statementRAISE
errors/warnings for the logs or clientEXCEPTION
blocks instead of having the whole transaction terminate on errorCASE ... WHEN
very wellWITH
and CTEsWith 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.