How to design a transaction that calls nested functions and only commits if all called nested functions commit, e.g. abort if one subfunction fails?
For example, this transaction:
CREATE OR REPLACE FUNCTION unfollow(
should only commit if both update functions are successful. This works when I add to every database operation in the called functions a statement that checks if the previous CRUD operation was successful and raises an exception if unsucessful. Like:
CREATE OR REPLACE FUNCTION delete_relationship(
follower = follower_id
AND following = following_id;
IF NOT FOUND THEN
-- Raise an exception to roll back the transaction
RAISE EXCEPTION 'Error in unfollow_transaction: %', SQLERRM;
If I do not raise that exception, the transaction commits even one of the sub function fails.
Is there a smarter way to do this? Is it possible to make functions raise an exception by default if a CRUD operation fails without mentioning it every time, e.g. is there a way to avoid writing all the exception statements?
Failing to select update or delete rows that match a where clause is not exceptional.
However you don't need to rollback anything. Any exception - whether from the dB itself or your code does that. That is basically the whole point of transactions. Everything works or nothing does.
Additon in response to the comment below.
It's not exceptional to affect zero rows because... there's nothing unusual about that.
UPDATE enquiries SET status='expired'
WHERE status='pending' AND query_date < CURRENT_DATE - 365
That's the sort of query that you might run every night from a scheduled task. Why exactly should it throw an exception if there aren't any old pending enquiries? That's a perfectly normal situation.