Mastering PostgreSQL 12 - Third Edition

Understanding fundamentals – stored procedures versus functions

Before we dig into the anatomy of a stored procedure, it is important to talk about functions and procedures in general. The term stored procedure has traditionally been used to actually talk about a function. Thus, it is essential that we understand the difference between a function and a procedure.

A function is part of a normal SQL statement, and is not allowed to start or commit transactions. Here is an example:

SELECT func(id) FROM large_table;

Suppose func(id) is called 50 million times. If you use the function called commit, what exactly should happen? It is impossible to simply end a transaction in the middle of a query and launch a new one. The entire concept of transactional ...