The term, PL/SQL is a procedural extension of SQL. SQL is commonly used by relational database technologies such as Oracle, Microsoft Access, Sybase etc. It's worth remembering that plain SQL functions can contain more than one statement the last statement is the function's result.SQL, that is abbreviated from Structured Query Languages, is a data oriented language for selecting and operating sets of data. I use views and plain SQL functions a lot more now. 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.

You need to perform an action after producing the result set.

Lots of re-use of calculated values that you can't do fit into WITH and CTEs.Complex conditional logic that doesn't fit CASE.When you need exception handling - you can trap and handle errors with EXCEPTION blocks instead of having the whole transaction terminate on error.When you want to RAISE errors/warnings for the logs or client.Dynamic SQL and dynamic DDL via the EXECUTE statement.Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for: 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. The main time SQL functions won't do the job is when you need lots of logic. Use SQL functions whenever they're sufficient for the task. 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. SQL functions declared STABLE or IMMUTABLE (and not also declared STRICT or SECURITY DEFINER) can also be inlined into the calling statement. 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.ДЇor most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL.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.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.Parameters that can't be expressed as simple WHERE clauses are needed, like a parameter within a WITH expression.The main time you find you can't use a view and should consider an SQL function is when: I've often had big performance improvements from converting SQL functions into views.

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. Very frequently a view is sufficient when you think a function is needed. and where no PL will do the job, use an external program and possibly LISTEN and NOTIFY to talk to it.

Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is.

Where an SQL function isn't suitable, use PL/PgSQL.Where a view is not suitable, use an SQL function.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: PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context.

Sql vs pl sql