Postgresql - Trying a rollback in a function

Question

The structure of relation between tables presented below is not the most convenient and technical. Just to illustrate what I need.

I have read many articles about Function, Trigger functions and I have a problem, because in one part I got an answer: I need to update two different tables in the same procedure and check if the second table is consistent with the first.

Explain:

a) I have a table (tabinvoice) that stores issued invoices and has a field for the 'invoice number', another for the 'financial volume' of orders that are part of an invoice and another to indicate the 'status' of pending or paid invoices.

b) I have another table that keeps a reference to orders (tabs) in the previous table, which has the fields 'invoice number' and 'status' of orders that are part of a paid or unpaid invoice. The reference between the two tables is the 'invoice number', which are not indexes by nature.

c) Finally, if there is a 'low' in the payment tab, I need to indicate in tabpedidos the paid status.

I need to make sure that all records of the table tabpedidos will be downloaded if there is a low in the tabfacturas, changing in both the code of the status field to 'downloaded'.

But I cannot allow the sum of the orders linked to a particular invoice not to 'match' with the value marked in the tabinvoice, if inadvertently someone may have 'tampered' in the tabpedidos for some reason.

I would use, for this, a function written in plpgsql.

However, from what I understand, I can not use ROLLBACK inside function nor SAVEPOINT, if, for any reason, the 'financial volume' of the orders marked in the table tabfacturas does not correspond to the sum of the values of the orders.

That is, if the values differ, the tabinvoice update needs to be undone.

There is a 'rollback' or I will have to do, within the function, an IF THEN ELSE structure to test values before the update?

Answer 1

According to this OS post (https://stackoverflow.com/questions/12778209/are-postgresql-functions-transactional), functions inherit the behavior of the transaction in which they were called.

If the function call is in a transaction, the function behaves appropriately. So, for everything to work as expected, you just need to ensure that the function is called in a transaction, either via SQL, or by some other program that generates the SQL query and sends it to the database.