How to create trigger and function using arguments dinamicaly?

user_1243068 0
Viewed 26

By example, have a table with 3 columns: a, b & c, in the function need set one of tree columns, by example:

insert into test (a, b, c) values (...)

But need call the function from trigger, by example, when update a row set a column to true, when insert set b and when delete set c.

I try this:

CREATE OR REPLACE FUNCTION function_test(s_a, s_b, s_c)
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
BEGIN
    INSERT INTO test(a, b, c)
    VALUES(s_a, s_b, s_c);
    RETURN NEW;
END;
$$

But postgres says: s_a does not exist.

My idea is create multiple trigger with this arguments, like as:

CREATE TRIGGER trigger_test
    AFTER INSERT
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE function_test(true, false, false);

CREATE TRIGGER trigger_test
    AFTER UPDATE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE function_test(false, true, false);

My idea is create a audit table with current changes by username.

postgresql
1 Answers

In your example, you don't need to pass arguments to the trigger function. You can simply do for the INSERT :

CREATE OR REPLACE FUNCTION before_insert_test()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
BEGIN
    NEW.a = TRUE;
END;
$$

CREATE TRIGGER before_insert_test
BEFORE INSERT
ON test
FOR EACH ROW
EXECUTE PROCEDURE before_insert_test();

For the UPDATE trigger, you can do the same while replacing NEW.a = TRUE by NEW.b = TRUE.

PS : you can't update the NEW values in an AFTER INSERT or AFTER UPDATE trigger because the fields are updated in the database right after the BEFORE trigger has been executed and before the AFTER trigger is executed.

user_80600170
Related