Trigger raising: "ERROR: stack depth limit exceeded"

Question

I am trying to make a trigger where after I insert a painting, then I want to insert it to either the In_Gallery table or the On_Loan table but not both. When I tried to make a trigger function, I keep getting the error:

ERROR: stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

I am not sure what's wrong with this:

    CREATE OR REPLACE FUNCTION checkOnLoan()    
    RETURNS trigger AS
$$
    DECLARE    
       countGal numeric;
    BEGIN
            SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
            IF countGal = 0 THEN    
                INSERT INTO ON_LOAN VALUES (new.Certid, new.P_id, new.Insurer);
            ELSE
                RAISE EXCEPTION 'ALREADY IN GALLERY';
            END IF;
    RETURN new;
    END;
$$

LANGUAGE 'plpgsql';

CREATE TRIGGER OnLoan
    AFTER INSERT ON ON_LOAN
    FOR EACH ROW
    EXECUTE PROCEDURE checkOnLoan();

Answer 1

You INSERT again in an AFTER INSERT trigger, causing the trigger to be fired again for this second INSERT which again INSERTs and fires the trigger anew and so on and so on. At some point the stack is exhausted from all that function calls and you get the error.

Remove the INSERT from the trigger functions and just RETURN new. Returning new will cause the original INSERT to be completed. There's no need for a manual INSERT in the trigger function for AFTER INSERT triggers.

Like:

CREATE OR REPLACE FUNCTION checkOnLoan()    
RETURNS trigger AS
$$
DECLARE    
    countGal numeric;
BEGIN
    SELECT COUNT(*) INTO countGal FROM IN_GALLERY WHERE P_id = new.P_id;
    IF countGal = 0 THEN    
        RETURN new;
    ELSE
        RAISE EXCEPTION 'ALREADY IN GALLERY';
    END IF;
END;
$$
LANGUAGE plpgsql;

And analog for the other trigger function.