R: Catch exception from plpy

Hi Martin,

Thanks for your help ...But I still don' get it!

I edited your code to correct what I think are some typos but I still get the
error message. What am I doing wrong!?

Dario

--
create or replace function test_tryex() returns void AS
$$
try:
    plpy.execute('create table tmp_foo (v1 int);')
    plpy.execute('select * from tmp_foo);')
except Exception, ex:
    plpy.notice("FUBAR!-- %s" % str(ex))
    return "FUBAR!"
return "test_tryex function has succeeded"
$$
    LANGUAGE plpythonu;
--
select test_tryex();
--
WARNING:  plpython: in function test_tryex:
DETAIL:  <class 'plpy.SPIError'>: Unknown error in PLy_spi_execute_query
NOTICE:  ('FUBAR!-- error return without exception set',)

ERROR:  relation "tmp_foo" already exists
CONTEXT:  SQL statement "create table tmp_foo (v1 int);"

********** Error **********

ERROR: relation "tmp_foo" already exists
SQL state: 42P07
Context: SQL statement "create table tmp_foo (v1 int);"

--




>----Messaggio originale----
>Da: dario.ber@libero.it
>Data: 24/04/2010 14.48
>A: <pgsql-general@postgresql.org>
>Ogg: Catch exception from plpy
>
>Hello,
>
>Could someone show me how to catch exceptions generated by plpy.execute()?
From the documentation and other posts I understand that you need to call
plpy.
>error() but I still cannot figure out how to use it.
>
>For example, say I need a function that creates table foo if it doesn't
>exists, otherwise returns the rows in foo. Initially I thought the following
>shouldl work... but it doesn't!
>
>--
>create or replace function test_tryex() returns void AS $$
>    try:
>        plpy.execute('create table tmp_foo (v1 int);')
>    except:
>        plpy.execute('select * from tmp_foo;')
>    $$
>language plpythonu;
>--
>-- If tmp_foo already exists I get:
>select test_tryex();
>
>********** Error **********
>
>ERROR: relation "tmp_foo" already exists
>SQL state: 42P07
>Context: SQL statement "create table tmp_foo (v1 int);"
>
>
>Many thanks in advance
>Dario