Mastering PostgreSQL 12 - Third Edition

Using functions and transactions

As you know, everything that PostgreSQL exposes in user land is a transaction. The same, of course, applies if you are writing functions. A function is always part of the transaction you are in. It is not autonomous, just like an operator or any other operation.

Here is an example:

test=# SELECT now(), mysum(id, id) FROM generate_series(1, 3) AS id; 
 now                           | mysum 
-------------------------------+------- 
 2017-10-12 15:54:32.287027+01 |    2 
 2017-10-12 15:54:32.287027+01 |    4 
 2017-10-12 15:54:32.287027+01 |    6  
(3 rows) 

All three function calls happen in the same transaction. This is important to understand because it implies that you cannot do too much transactional flow control inside a function. What happens when ...