Stack overflow in stored procedure (PostgreSQL)

Question

Stack overflow in stored procedure (PostgreSQL)

Navigation

#1 by (0 votes)

-1

I have this procedure stored in PostgreSQL:

CREATE FUNCTION ratpais_indcicloinmo() RETURNS trigger AS $ratpais_indcicloinmo$
    BEGIN
        UPDATE sde.ratpais SET indcicloinmo=(pond1cicloinmo)*(pond2cicloinmo)*(cicloinmo);
    END;

$ratpais_indcicloinmo$ LANGUAGE plpgsql;

CREATE TRIGGER ratpais_indcicloinmo BEFORE INSERT OR UPDATE ON sde.ratpais
    FOR EACH ROW EXECUTE PROCEDURE ratpais_indcicloinmo();

The problem is that when I execute it next to its trigger, it gives me 4 stack overflow error because you can not use UPDATE in the function and the trigger because it generates a recursion that overflows the stack, but I do not know how to eliminate this problem ...

The only thing I need with this SQL code is to multiply 3 fields and save the result in another field already created, and this should be automatically done (thanks to the trigger) each time a new value is inserted in the table.

Thank you very much for your time!

sql postgresql

asked by Ádrian Knight 21.12.2017 в 21:27

source

1 answer

When I give in my onclick event sending a date I am left Doubt at ManyToManyFields Django

score 0 · Answer 1

Indeed, you should not use a UPDATE statement to update a column in a trigger. Rather, in the trigger you already have direct access to the record that is being modified and you can directly access and modify the columns in this record using the NEW.columna notation:

CREATE FUNCTION ratpais_indcicloinmo() RETURNS trigger AS $ratpais_indcicloinmo$
    BEGIN
        NEW.indcicloinmo := NEW.pond1cicloinmo * NEW.pond2cicloinmo * NEW.cicloinmo;
        RETURN NEW;
    END;
$ratpais_indcicloinmo$ LANGUAGE plpgsql;