Re: plpgsql exception handling

On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> wrote:

> same same.
> all errors including syntax_error match to others, but I checked it again.
> and the exception remains.
>

I'm just guessing here, but is it throwing a new exception in the exception
handler? I realize that the exception that is getting caught at the top
level is the original exception, but maybe that's expected behaviour when an
exception handler throws a new exception - it re-throws the original? What
happens if you simplify the exception handler down to just a really simple
log message? There's really nothing else obviously wrong with the code you
provided, so I'm hoping it is a problem in the code that you didn't include
since nothing else makes sense.

I just executed this and it worked correctly - saw my error statement from
the exception handler:

CREATE OR REPLACE FUNCTION report_process(p_sql text)
RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN

l_state=0;
begin
execute 'create table result_9 as '||p_sql;
exception when others then
l_state=-3;
raise notice 'error';
end;
return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
cost 100;

# select report_process('select 1count(*) from accounts'::text);
NOTICE: error
report_process
----------------
0
(1 row)