recursion - Prevent recursive trigger in PostgreSQL

Question

How to prevent recursive execution of trigger? Let's say I want to construct a "tree-able" description on chart of account. So what I do is when a new record is inserted/updated, I update the the parent record's down_qty, so this would trigger the update trigger recursively.

Right now, my code is ok - I put this on UPDATE trigger's first line:

-- prevents recursive trigger
if new.track_recursive_trigger <> old.track_recursive_trigger then
    return new;
end if;

And this is the sample code from my trigger when I need to update the parent record's qty:

update account_category set 
    track_recursive_trigger = track_recursive_trigger + 1, -- i put this line to prevent recursive trigger
    down_qty = down_qty - (old.down_qty + 1)
where account_category_id = m_parent_account;

I'm thinking if there's a way in PostgreSQL to detect recursive trigger without introducing a new field, something analogous to MSSQL's trigger_nestlevel.

[EDIT]

I loop inside the tree, I need to bubble up the down_qty of each account_category back to its root. For example, I insert a new account category, it needs to increment the down_qty of its parent account_category, likewise when I change the account category's parent account_category, I need to decrement the down_qty of account_category's previous parent account_category. Though I think it can, I'm not letting PostgreSQL do the recursive trigger. I used MSSQL before where the trigger recursive depth level is limited only up to 16 levels.

Answer 1

This is what I do in PostgreSQL 9.2, although I must admit I did not find this approach documented. There is a function pg_trigger_depth() documented here, which I use to differentiate between original and nested calls in the trigger.

CREATE TRIGGER trg_taxonomic_positions
AFTER INSERT OR UPDATE OF taxonomic_position
ON taxon_concepts
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE trg_taxonomic_positions()