Mastering PostgreSQL 10

Using functions and transactions

As you know, everything that PostgreSQL exposes in userland is a transaction. The same, of course, applies if you are writing stored procedures. The procedure is always part of the transaction you are in. It is not autonomous--it is 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. Suppose ...