CODEWITHSUNDEEP
postgresqlplpgsql
Mehran
Mehran

Reputation: 16831

Unexpected error thrown in PostgreSQL's function and not catched

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?

Upvotes: 0

Views: 116

Answers (1)

user330315
user330315

Reputation:

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;
$$;

Upvotes: 1

Related Questions