Postgres trigger does not find return

Question

Here is my trigger:

CREATE OR REPLACE FUNCTION update_played ()
  RETURNS trigger
AS
$BODY$
  DECLARE
    v_count_played integer;
BEGIN

    SELECT count(*) INTO STRICT v_count_played FROM history WHERE song_id = NEW.song_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
      WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';

    EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
        USING v_count_played, NEW.song_id;

    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


create trigger update_played_trigger
after insert on history
for each row
execute procedure update_played();

It gives the error:

sqlalchemy.exc.InternalError: (psycopg2.InternalError) control reached end of trigger procedure without RETURN

Craig Ringer · Accepted Answer · 2015-07-30 01:59:41Z

1

You've misunderstood the EXCEPTION keyword. If I change your indenting to match how it works it might help you understand. EXCEPTION appears with BEGIN and END as part of a block like a try {} catch {} in other languages. So it works like this:

BEGIN

    SELECT count(*) INTO STRICT v_count_played 
    FROM history WHERE song_id = NEW.song_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';

        EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
            USING v_count_played, NEW.song_id;

        RETURN NULL;
END;

What happens here is that you run the SELECT. If there's a NO_DATA_FOUND exception you RAISE. If there's a TOO_MANY_ROWS exception you RAISE, then in the same block there is unreachable code after the RAISE EXCEPTION that does an EXECUTE and RETURN.

If there is no exception from the SELECT then no further action is taken, the procedure simply exits, returning nothing.

What I think you intended to write was:

BEGIN
    BEGIN
      SELECT count(*) INTO STRICT v_count_played FROM history WHERE song_id = NEW.song_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'history count for song % not found', NEW.song_id;
      WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'history count did not aggregate';
    END;

    EXECUTE 'UPDATE song SET count_played = $1 WHERE id = $2'
        USING v_count_played, NEW.song_id;

    RETURN NULL;
END;

answered Jul 30, 2015 at 1:59

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

Tjorriemorrie Over a year ago

Thanks for the clear answer. I noticed it ran (now that is is fixed) for all the row in history, although it didn't on subsequent inserts. Is that how triggers work? If I add a new trigger it will on its first run do it for every existing row even if only one new row is inserted?

Craig Ringer Over a year ago

@Tjorriemorrie Er. No. Triggers do nothing for existing rows and fire only for new rows. I suspect your trigger logic isn't right. If you don't figure it out please post a new question and link back to this one for context.

Collectives™ on Stack Overflow

Postgres trigger does not find return

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related