4

Given a function like this:

CREATE OR REPLACE FUNCTION foo()
RETURNS TRIGGER AS $$
BEGIN
    -- do something fancy
END;
$$
LANGUAGE plpgsql;

which is used in a TRIGGER:

CREATE TRIGGER bar
    BEFORE INSERT
    ON test
    FOR each ROW
    EXECUTE PROCEDURE foo();

Does the TRIGGER need to be re-created (DROP + CREATE) if the function foo() is changed (using REPLACE)?

Was coming across this while reading about partitioning.

2
  • No, you can simply replace the function. Why not test it yourself? Commented Jan 13, 2016 at 6:26
  • 1
    I tried, but I got confused by an example that I found somewhere. Can't recollect where I saw it unfortunately. Now I even found it in the official docs: "The trigger definition does not need to be updated, however." here
    – kev
    Commented Jan 14, 2016 at 5:24

1 Answer 1

1
-- Create the function foo()
CREATE OR REPLACE FUNCTION foo()
RETURNS TRIGGER AS $$
BEGIN
    -- do something fancy
    -- You should include the appropriate logic here.
    RETURN NEW; -- For example, return NEW; if it's a row-level trigger.
END;
$$
LANGUAGE plpgsql;

-- Create the trigger bar
CREATE TRIGGER bar
    BEFORE INSERT
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE foo();

use the CREATE OR REPLACE FUNCTION syntax to modify the function foo(), the trigger bar will automatically use the updated function without needing to be re-created (DROP + CREATE). The trigger will automatically reference the latest version of the function definition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.