How to create trigger and function using arguments dynamically?

Question

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.

I would spend some time here plpgsql trigger function. To start : 1) "Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER — such arguments are passed via TG_ARGV, as described below" 2) "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." — Adrian Klaver
– Adrian Klaver, Commented Nov 15, 2021 at 18:20

Edouard · Accepted Answer · 2021-11-15 20:02:49Z

1

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.

answered Nov 15, 2021 at 20:02

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create trigger and function using arguments dynamically?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related