2

In a PL/pgSQL function, RAISE EXCEPTION 'mymessage' USING ...; will have “mymessage” as the error message, but in a PL/Python function, plpy.error('mymessage', ...) will have “plpy.Error: mymessage” as the error message. Is there a straightforward way to remove that prefix from the error message?

Zachary Yaro
  • 196
  • 12

2 Answers2

2

If you really MUST have consistency in the exception messages you can wrap the python function in a plpgsql function and edit the message.

CREATE OR REPLACE FUNCTION my_fn_py()
    RETURNS VOID
    LANGUAGE plpython3u VOLATILE
    AS $python$
try:
    # stuff... pretend this is a big complicated thing ...

except Exception as e:
    plpy.error('custom_error', detail = str(e))
$python$;

-- Wrapper fn to normalize the plpython exceptions
CREATE OR REPLACE FUNCTION my_fn()
    RETURNS VOID
    LANGUAGE plpgsql VOLATILE
    AS $body$
DECLARE
    _msg TEXT;
    _detail TEXT;
BEGIN
    PERFORM my_fn_py();

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            _msg = MESSAGE_TEXT,
            _detail = PG_EXCEPTION_DETAIL;

        IF _msg = 'plpy.Error: custom_error' THEN
            RAISE EXCEPTION 'custom_error' -- can't use a variable here :(
                DETAIL := _detail,
                ERRCODE := P0099;
        ELSIF /* other cases... */ THEN
        ELSE
            -- Re-raise the original exception
            RAISE;
        END IF;
END
$body$;
user9645
  • 6,286
  • 6
  • 29
  • 43
1

I afraid so you cannot to change it anyway. It's by design. It has little bit different semantics. Inside plpgsql you can raise direct PostgreSQL exception. It is not possible in Python - the python exceptions plpy.Error is raised, catched and transformed to PostgreSQL exceptions - the "plpy.Error" is name of exception. There can be any name of any other exception.

The convention of Python exceptions in this case is - level: name: text

you can see

ERROR:  ZeroDivisionError: division by zero
ERROR:  plpy.Error: some text

and there is not possibility how to change it. This is by design - this is convention.

PLpgSQL has not this convention - but PLpgSQL is much more integrated to Postgres. It has not own environment, own exceptions.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • The message format is very important to us as it is returned to the user. If the plpython function is wrapped in a plpgsql function, can the wrapper function trap the exception from the python function and re-raise it as a postgres exception? – user9645 Oct 27 '20 at 11:14
  • @user9645 - it is possible, but exception handling in plpgsql has performance costs. – Pavel Stehule Oct 27 '20 at 11:49