plpythonu: how to catch plpy.execute() exceptions - Mailing list pgsql-admin

Hello all,

I am trying to create a plpythonu function that will execute some SQLs
stored in a table (because it should work much faster than executing
from remote).

The thing is that I cannot catch the exception raised by plpy.execute().

I tried this with the following code:

CREATE OR REPLACE FUNCTION __syncpg_execute() RETURNS boolean
AS $$
        try:
                plpy.execute("SELECT * FROM not_existing_table", 5)
        except Exception, exc:
                plpy.notice("Exception %s" % str(exc) )
        return True
$$ LANGUAGE plpythonu;

The result is:
# select __syncpg_execute();
WARNING:  PL/Python: plpy.SPIError: unrecognized error in PLy_spi_execute_query
CONTEXT:  PL/Python function "__syncpg_execute"
NOTICE:  Exception error return without exception set
CONTEXT:  PL/Python function "__syncpg_execute"
ERROR:  relation "not_existing_table" does not exist
LINE 1: SELECT * FROM not_existing_table
                                      ^
QUERY:  SELECT * FROM not_existing_table
CONTEXT:  PL/Python function "__syncpg_execute"

I am using Postgresql9.0 with python 2.4.3

Thank you

--
Best regards,
Dragos Moinescu