2

Consider the following (incomplete) block of PL/pgSQL inside a function:

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

When an unanticipated error occurs, this code will throw a new exception with the same message. However, it won't preserve the original type or context.

How can I reraise or rethrow the original exception unmodified?

  • If anyone is curious, I am writing this question because of the dearth of relevant results when Googling "postgresql reraise exception" and because the answer is neatly tucked away near the bottom of the documentation (and uses the word "rethrow" instead of "reraise"), making it harder to find. – jpmc26 Jan 30 at 22:58
4

You can use RAISE without any parameters. This is documented on the Errors and Messages page:

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's EXCEPTION clause; it causes the error currently being handled to be re-thrown.

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE;
        END IF;
    END
    $$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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