Mastering PostgreSQL 9.6

Handling errors

Once in a while, you might have to catch an error. Of course, this is also possible in Python. The following example shows how this works:

CREATE OR REPLACE FUNCTION trial_error()         RETURNS text AS $$     try:         rv = plpy.execute("SELECT surely_a_syntax_error")     except plpy.SPIError:         return "we caught the error"     else:         return "all fine" $$ LANGUAGE 'plpythonu'; 

You can use a normal try/except block and access plpy to treat the error you want to catch. The function can then return normally without destroying your transaction:

test=# SELECT trial_error();      trial_error      ---------------------  we caught the error (1 row) 

Remember, PL/Python has full access to the internals of PostgreSQL. Therefore, it can also expose all kinds ...