Thread: SQLSTATE and SQLERRM
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
Attachment
Pavel Stehule wrote: > 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) Seems OK to allow them to be visible outside an exception. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Seems OK to allow them to be visible outside an exception.
Pavel just told me off-list that he'd reconsidered and now likes
the other way better. So please hold off applying this patch;
there will be another one by and by.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Seems OK to allow them to be visible outside an exception. > > Pavel just told me off-list that he'd reconsidered and now likes > the other way better. So please hold off applying this patch; > there will be another one by and by. Roger. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch retracted by author; new one coming. --------------------------------------------------------------------------- Pavel Stehule wrote: > 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 Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 7 Jun 2005, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Seems OK to allow them to be visible outside an exception. > > > > Pavel just told me off-list that he'd reconsidered and now likes > > the other way better. So please hold off applying this patch; > > there will be another one by and by. > > Roger. > > I am sorry :-)). Next version will be finall. There is not easy speak what is better. I desided correct my patches to be more light. Pavel