Postgres Pro
Downloads
Home   >   mailing lists

Thread: Exception ERROR Code

Exception ERROR Code

From
Ali Baba
Date:
Hi ,

I am looking for the way to get the error code
corresponding to the exception in plpgsql.
Can any body guide me.

Thanks,
Asif Ali

    
__________________________________ 
Celebrate Yahoo!'s 10th Birthday! 
Yahoo! Netrospective: 100 Moments of the Web 
http://birthday.yahoo.com/netrospective/

Re: Exception ERROR Code

From
Michael Fuhr
Date:
On Sat, Mar 05, 2005 at 06:03:20AM -0800, Ali Baba wrote:

> I am looking for the way to get the error code
> corresponding to the exception in plpgsql.

What exception?  Can you describe what you're trying to do?  Are
you using the EXCEPTION clause that's available in the latest
release, or are you hacking PL/pgSQL itself?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Exception ERROR Code

From
Pavel Stehule
Date:
Hello,

It's no possible now. But I prepared small patch which implemented 
variables sqlcode and sqlerrm for plpgsql. I can send it tomorrow. 

regards
Pavel Stehule

On Sat, 5 Mar 2005, Ali Baba wrote:

> Hi ,
> 
> I am looking for the way to get the error code
> corresponding to the exception in plpgsql.
> Can any body guide me.
> 
> Thanks,
> Asif Ali
> 
> 
>     
>         
> __________________________________ 
> Celebrate Yahoo!'s 10th Birthday! 
> Yahoo! Netrospective: 100 Moments of the Web 
> http://birthday.yahoo.com/netrospective/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

Implementation of SQLCODE and SQLERRM variables for PL/pgSQL

From
Pavel Stehule
Date:
Hello

  This is my second patch, than please will be tolerant :-). For one my
project I miss information about exception when I use EXCEPTION WITH
OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which
carry this information. With patch you can:


--
-- Test of built variables SQLERRM and SQLCODE
--

create or replace function trap_exceptions() returns void as $_$
begin
  begin
    raise exception 'first exception';
  exception when others then
    raise notice '% %', SQLCODE, SQLERRM;
  end;
  raise notice '% %', SQLCODE, SQLERRM;
  begin
    raise exception 'last exception';
  exception when others then
    raise notice '% %', SQLCODE, SQLERRM;
  end;
  return;
end; $_$ language plpgsql;

select trap_exceptions();

drop function trap_exceptions();

CREATE FUNCTION
NOTICE:  P0001 first exception
NOTICE:  000000 Sucessful completion
NOTICE:  P0001 last exception
 trap_exceptions
-----------------

(1 row)

DROP FUNCTION


Regards,
Pavel Stehule

Re: Implementation of SQLCODE and SQLERRM variables for PL/pgSQL

From
Tom Lane
Date:
Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
>   This is my second patch, than please will be tolerant :-). For one my
> project I miss information about exception when I use EXCEPTION WITH
> OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which
> carry this information.

I think we discussed this last year and decided that it would be a bad
idea to use those names because Oracle's use of them is not exactly
compatible with our error codes and messages.  SQLCODE in particular is
not compatible at all --- it's an integer in Oracle, isn't it?

IIRC we had put off solving this problem until we decided what to do
with RAISE.  There really needs to be some changes in RAISE to allow it
to raise a specific error code rather than always P0001, but exactly
what is still undecided.

Some other problems with your patch: no documentation, and not in
diff -c format.  Plain diff patches are never acceptable because
it's too risky to apply them against files that might have changed
since you started working with them.  Also, it's much easier to
deal with one patch than with a separate diff for each file.
(diff -c -r between an original and a modified directory is one
good way to produce a useful patch.)

            regards, tom lane

Re: Implementation of SQLCODE and SQLERRM variables for

From
Pavel Stehule
Date:
>
> I think we discussed this last year and decided that it would be a bad
> idea to use those names because Oracle's use of them is not exactly
> compatible with our error codes and messages.  SQLCODE in particular is
> not compatible at all --- it's an integer in Oracle, isn't it?

There is more incompatibilities to Oracle. SQLERRM is function on Oracle,
only if you use it without parametr, returns current message error.
SQLCODE is really integer. But it's only names. There is no problem change
it.

>
> IIRC we had put off solving this problem until we decided what to do
> with RAISE.  There really needs to be some changes in RAISE to allow it
> to raise a specific error code rather than always P0001, but exactly
> what is still undecided.

I didn't know it. But for my work is SQLERRM more important. I have more
constraints on tables and I need detect which which constraints raise
exception. The possibility EXCEPTION WITH OTHERS is nice, but not too much
usefull because I have not possibility get some informations about except.

>
> Some other problems with your patch: no documentation, and not in
> diff -c format.  Plain diff patches are never acceptable because
> it's too risky to apply them against files that might have changed
> since you started working with them.  Also, it's much easier to
> deal with one patch than with a separate diff for each file.
> (diff -c -r between an original and a modified directory is one
> good way to produce a useful patch.)
>

I am not sure, I able create documentation - my english is poor. I will
change diff's format and send patch again.

Thank you
Pavel