How do I reraise an exception in a PL/pgSQL EXCEPTION block?

Question

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?

score 19 · Accepted Answer · answered Jan 30 '19 at 22:57

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

How do I reraise an exception in a PL/pgSQL EXCEPTION block?

1 Answers1