On Thu, Mar 10, 2011 at 2:53 AM, Uwe Bartels
<uwe.bartels@gmail.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)