CODEWITHSUNDEEP
postgresql
e-info128
e-info128

Reputation: 4072

How to create trigger and function using arguments dynamically?

For example, I have a table with 3 columns: a, b & c, in the function need set one of three columns, for example:

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

But need call the function from trigger, for 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, such 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.

Upvotes: 2

Views: 152

Answers (1)

Edouard
Edouard

Reputation: 7065

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.

Upvotes: 1

Related Questions