Unexpected error thrown in PostgreSQL's function and not catched

Question

I'm trying to make a list of users who are modified and then at the end of transaction I would like to process the list. Here's how I'm trying to do this:

CREATE TABLE "user"(
    user_id bigserial NOT NULL,
    username varchar(255) NOT NULL,
    CONSTRAINT pk_5 PRIMARY KEY (user_id)
);

CREATE FUNCTION enlist_user ()
        RETURNS trigger
        LANGUAGE plpgsql
        AS $$
DECLARE
    rec record;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        rec := NEW;
    ELSE
        rec := OLD;
    END IF;

    CREATE TEMPORARY TABLE IF NOT EXISTS user_list (
        user_id bigint NOT NULL,
        CONSTRAINT user_queue_pk PRIMARY KEY (user_id)
    );

    INSERT INTO user_list
    VALUES (rec.user_id);

    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Unable to add %', rec.user_id;

    RETURN rec;
END;
$$;

CREATE TRIGGER user_modified
        AFTER INSERT OR DELETE OR UPDATE
        ON "user"
        FOR EACH ROW
        EXECUTE PROCEDURE enlist_user();

INSERT INTO "user" VALUES (default, 'qwe');

But running the above code results in follow error:

ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function enlist_user()

What's going on? Why RETURN rec did not work?

Your return rec is inside the exception block (which is hidden by your "wrong" indention). If no exception is thrown that line is never reached. — user330315
– user330315, Commented Aug 4, 2015 at 14:06
@a_horse_with_no_name Could you please correct it? How should I terminate the exception's block? — Mehran
– Mehran, Commented Aug 4, 2015 at 14:08
The exception block is terminated with the end that you already have. But if no exception is raised, it won't be executed. Logically the keyword exception is on the same level as your begin - the indention you have is misleading. — user330315
– user330315, Commented Aug 4, 2015 at 14:17

user330315 · Accepted Answer · 2015-08-04 14:46:10Z

Your indention is misleading. The exception part belongs logically on the same level as the first begin:

CREATE FUNCTION enlist_user ()
        RETURNS trigger
        LANGUAGE plpgsql
        AS $$
DECLARE
    rec record;
BEGIN
    ...

EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Unable to add %', rec.user_id;

  RETURN rec; -- << only reached if an exception is thrown.
END;
$$;

So the RETURN is part of the exception block and thus only reached if an exception occurs. What you actually want is:

CREATE FUNCTION enlist_user ()
        RETURNS trigger
        LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
BEGIN
    ...

  RETURN rec; -- normal flow of the function

EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Unable to add %', rec.user_id;

END;
$$;

You also shouldn't just do a raise notice which means that the original exception won't be shown to the end user. The caller also will not have any clue that an exception happened. It's better if you re-raise the exception that you handled:

  .... 
  RETURN rec; -- normal flow of the function

EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Unable to add %', rec.user_id;
  RAISE; -- signal this error to the caller
END;
$$;

Collectives™ on Stack Overflow

Unexpected error thrown in PostgreSQL's function and not catched

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