Mastering PostgreSQL 12 - Third Edition

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: 
else: 
  return "all fine" 
$$ LANGUAGE 'plpythonu'; 

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

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