Hello
This patch respect last Tom's Lane notes. Is based on first variante,
http://archives.postgresql.org/pgsql-committers/2005-05/msg00303.php
SQLSTATE and SQLERRM are local variables blocks with EXCEPTION clause.
I wonted respect a litlle bit Oracle behavior and therefore exist these
variables on function scope too, allways "00000" and "Successful
completation" (allways marked isconst).
I am really not sure what is better, respect Oracle or not with visibility
SQLSTATE and SQLERRM on function scope. Is true, using these variables
outside EXCEPTION is nonsens, but on other hand all magic variables has
function scope: FOUND, NEW, OLD, ... . SQLSTATE and SQLERRM are little bit
similar. If are you in oportunity, please correct this patch. It's easy.
Clean all parts with these on functions related procedures. Then is
neccesery change documentation, add info about outside exceptions blocks
is impossible use SQLSTATE and SQLERRM variables. (regress too)
Regards
Pavel Stehule
create function excpt_test() returns void as $$
begin
raise notice '% %', sqlstate, sqlerrm;
begin
raise exception 'usr exception';
exception when others then
raise notice 'caught usr exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught usr exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end; $$ language plpgsql;
CREATE FUNCTION
select excpt_test();
NOTICE: 00000 Successful completion
NOTICE: caught usr exception P0001 usr exception
NOTICE: P0001 usr exception
NOTICE: caught usr exception 22012 division by zero
NOTICE: P0001 usr exception
NOTICE: 00000 Successful completion
excpt_test
------------
Regards
Pavel Stehule
