Database Reference
In-Depth Information
want it to work with a value of any Python type, wrapping the value in
str(value)
usually works well.
Handling exceptions
With any bit of code, you need to make sure you handle when errors occur and your
PL/Python functions are not an exception.
Before Version 9.1 of PostgreSQL, any error in an SQL query caused the surround-
ing transaction to be rolled back:
hannu=# DO LANGUAGE plpythonu $$
hannu$# plpy.execute('insert into ttable
values(1)')
hannu$# plpy.execute('fail!')
hannu$# $$;
ERROR: spiexceptions.SyntaxError: syntax error
at or near "fail"
LINE 1: fail!
^
QUERY: fail!
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 3, in
<module>
plpy.execute('fail!')
PL/Python anonymous code block
You can manually use the
SAVEPOINT
attributes to control the boundaries of the
rolled-back block, at least as far back as Version 8.4 of PostgreSQL. This will reduce
the amount of the transaction that is rolled back:
CREATE OR REPLACE FUNCTION
syntax_error_rollback_test()
RETURNS void
AS $$
plpy.execute('insert into ttable values(1)')
try:
