SQLSTATE and SQLERRM

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLSTATE and SQLERRM

Pavel Stehule-5
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  


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]

sqlstate.diff (18K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQLSTATE and SQLERRM

Bruce Momjian-2
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
  [hidden email]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: SQLSTATE and SQLERRM

Tom Lane-2
Bruce Momjian <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: SQLSTATE and SQLERRM

Bruce Momjian-2
Tom Lane wrote:
> Bruce Momjian <[hidden email]> 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
  [hidden email]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: SQLSTATE and SQLERRM

Bruce Momjian-2
In reply to this post by Pavel Stehule-5

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 [hidden email]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  [hidden email]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: SQLSTATE and SQLERRM

Pavel Stehule-5
In reply to this post by Bruce Momjian-2
On Tue, 7 Jun 2005, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster