postgresql trigger error control reached end of trigger without RETURN

Question

Edit: I have added the updated code to my post, I was missing a return null outside my IF block

I have a table working with the most recent data and would like to add a trigger for updates made to it so that I can record the changes made to a particular column in another table history. I run this code to create a trigger function and test it but get an error

SQL Error [2F005]: ERROR: control reached end of trigger procedure without RETURN
  Where: PL/pgSQL function update_history()

My code:

-- create function for updates to track history
CREATE function update_history ()
RETURNS TRIGGER
as $$
BEGIN   
    IF NEW.discipline <> OLD.discipline THEN
    INSERT INTO history
        (ShiftId, fieldName, OldValue, NewValue)
    VALUES(New.shift_id, 'discipline', OLD.discipline, NEW.discipline);
    END IF;
return null; -- notice return outside the IF block
END;
$$
language plpgsql;


-- create trigger for my table after updates are made to the working table
create trigger update_history_trigger
after update on working
for each row execute PROCEDURE update_history();

klin · Accepted Answer · 2019-06-20 21:42:37Z

6

Your function cannot reach the RETURN statement when the condition is not met. Place the statement outside the IF block. The returned value is ignored and you can use NULL instead of NEW.

CREATE FUNCTION update_history ()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.discipline <> OLD.discipline THEN
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(NEW.shift_id, 'discipline', OLD.discipline, NEW.discipline);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Per the documentation:

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.

answered Jun 20, 2019 at 21:42

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

imlearningcode Over a year ago

Thanks! this worked great I've updated my post with the full solution

Collectives™ on Stack Overflow

postgresql trigger error control reached end of trigger without RETURN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related