Postgresql - Trying a rollback in a function

1

The relationship structure between tables below is not the most convenient and technical. It's just to illustrate what I need.

I've read quite a few articles about function, trigger functions and I'm having a problem, because in one part I got an answer: I need to update on two different tables in the same procedure and check if the second table is consistent with the first one.

I explain:

a) I have a table (tabfaturas) that keeps invoices issued and that has a field for 'invoice number', another for the 'financial volume' of the orders that comprise an invoice and another one to indicate the 'status' 'of outstanding or paid invoices.

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

c) Finally, if there is a 'write-down' in the pay tab, I need to flag the pay status on the tab page.

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

But I can not allow the sum of the requests linked to a certain invoice to not 'hit' the value indicated in the tabs, if inadvertently someone might have 'stirred' the tabped for any reason.

I would use for this a function written in plpgsql.

However, as I understand it, I can not use ROLLBACK within a function, nor SAVEPOINT, if, for any reason, the 'financial volume' of the orders indicated in the table of tabs does not correspond to the sum of the values of the requests. >

That is, if the values differ, the update of the tabs needs to be aborted.

Is there a 'rollback' or do I have to do an IF THEN ELSE structure to test values before the update?

    
asked by anonymous 10.11.2018 / 13:47

1 answer

1

According to this SO post ( link ), the roles inherit transaction behavior in which were called.

If the function call is in a transaction, the function behaves properly. 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.

    
10.12.2018 / 17:05