When to use stored procedure / user-defined function?

Question

I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database.

Is there any general advice to decide when to use them?

Answer 1

Postgres 11 or newer

SQL procedures (not functions) can COMMIT and start new transactions:

• The manual for CREATE PROCEDURE.

• 2ndquadrant blog entry introducing the feature.

However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.

And procedures can only return a single result row, yet, when created with INOUT parameters. Postgres 14 or later also allows OUT parameters.

Postgres 10 or older (original answer)

There are no "stored procedures" yet, strictly speaking. Just functions, doing almost but not quite the same. (The terminology is often mixed up!) Most importantly, functions always run inside a single transaction, which matters for lock management, trapping errors, or commands that cannot be run in a transaction context (see above). Related:

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Processing data in SQL can have major benefits over retrieving all raw data into your application and doing calculations there:

• What are the pros and cons of performing calculations in sql vs. in your application

The same principle can apply to bigger operations that can't be done in a single statement, not even when using "chained" commands in a (data-modifying) CTE:

• Are SELECT type queries the only type that can be nested?
• RETURN QUERY-Record in PostgreSQL

Depending on the use case, a function can be superior in performance, and often more convenient - especially when multiple apps access the same database. You have to chose a server-side language, most commonly SQL or PL/pgSQL:

• Difference between language sql and language plpgsql in PostgreSQL functions
• Function Performance

In the presence of competing concurrent transactions (multi-user environments) be brief. Only put as much into the same function as needs to be atomic. If possible, avoid long transactions with many locks. Locks are only released at the end of a transaction. Long transactions may stall concurrent access, even lead to deadlocks (which are detected and resolved by Postgres automatically, by raising an error for one or more competing transaction, which are then rolled back) ...

If you avoid those anti-patterns, server-side functions can be a great tool. For some purposes you must use functions anyway, like trigger functions.