CODEWITHSUNDEEP
postgresqltriggers
Jakub Małecki
Jakub Małecki

Reputation: 634

PostgreSQL - how to pass a function's argument to a trigger?

I have tree tables in a database: users (user_id (auto increment), fname, lname), roles (role_id, role_desc) and users_roles (user_id, role_id). What I'd like to do is to have a function create_user_with_role. The function takes 3 arguments: first name, last name and role_id. The function inserts a new row into the users table and a new user_id is created automatically. Now I want to insert a new record to the users_roles table: user_id is the newly created value and the role_id is taken from the function's arguments list. Is it possible to pass the role_id argument to an after insert trigger (defined on users table) so another automatic insert can be performed? Or can you suggest any other solution?

Upvotes: 1

Views: 4823

Answers (2)

Dan
Dan

Reputation: 1881

First @Pavel Stehule is right:

Don't try to pass parameters to triggers, ever!

Second, you just have to get the inserted id into a variable.

CREATE FUNCTION create_user_with_role(first_name text, last_name text, new_role_id integer)
RETURNS VOID AS $$
DECLARE
    new_user_id integer;
BEGIN
    INSERT INTO users (fname, lname) VALUES (first_name, last_name)
        RETURNING id INTO new_user_id;
    INSERT INTO users_roles (user_id, role_id)
        VALUES (new_user_id, new_role_id);
END;$$ LANGUAGE plpgsql;

Obviously, this is completely inefficient if you want to insert multiple rows but that's another question ;)

Upvotes: 3

Pavel Stehule
Pavel Stehule

Reputation: 45770

When you need to pass any parameter to trigger, then there is clean, so your design is wrong. Usually triggers should to have check or audit functionality. Not more. You can use a function, and call function directly from your application. Don't try to pass parameters to triggers, ever! Another bad sign are artificial columns in table used just only for trigger parametrization. This is pretty bad design!

Upvotes: 3

Related Questions