Thread: About how to use "exception when ??? then "
Hello, I am using PostgreSQL 8.0.1. In a function, I try to use exception to catch sql errors: begin .... begin exception WHEN ??? THEN end; ... end; The place where I have ???, what I should put there please? e.g., 1. WHEN sqlcode = '02000' THEN 2. WHEN no_data then 3. other ways? From the 8.0 docs, I am not be able to find Constant values of all error codes. http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html Please enlighten me. Thanks, Ying
Emi Lu wrote: > exception > WHEN ??? THEN > The place where I have ???, what I should put there please? > > e.g., > 1. WHEN sqlcode = '02000' THEN > 2. WHEN no_data then no_data See ch 35.7.5. "Trapping Errors" for an example > 3. other ways? > > From the 8.0 docs, I am not be able to find Constant values of all > error codes. > http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html From the page: "The PL/pgSQL condition name for each error code is the same as the phrase shown in the table, with underscores substituted for spaces. For example, code 22012, DIVISION BY ZERO, has condition name DIVISION_BY_ZERO. Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.)" I don't know if any have changed in 8.1, but there is a list of the codes in that version of the docs. http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html -- Richard Huxton Archonet Ltd
Hi Pedro, >|> The place where I have ???, what I should put there please? >|> >|> e.g., >|> 1. WHEN sqlcode = '02000' THEN >|> 2. WHEN no_data then >|> 3. other ways? >|> >|> From the 8.0 docs, I am not be able to find Constant values of all >|> error codes. >|> http://www.postgresql.org/docs/8.0/static/errcodes-appendix.html >|> >|> Please enlighten me. > >Maybe this link will help you better, as it has the Constants: > >http://developer.postgresql.org/docs/postgres/errcodes-appendix.html > >Then, some logic real life examples would be something like: > >(snip) >EXCEPTION > WHEN NOT_NULL_VIOLATION THEN > RAISE WARNING 'Not null...'; > WHEN OTHERS THEN > RAISE NOTICE 'Hmmmm.... [%,%]', SQLSTATE, SQLERRM; > >or > > > I am using postgresql 8.0.1. The keyword "SQLSTATE" & "SQLERRM" did not work for me. But, I think I do need the two outputs "sql error code", and "sql error code statement". Errors I got are: syntax error at or near "SQLSTATE" at character 2613 LINE 58: RAISE NOTICE 'Hmmmm.... [%,%]', SQLSTATE, SQL... Should I install any patches or do anything elese to have SQLSTATE and SQLERRM work for me? Ying
> <snip> From the page: > "The PL/pgSQL condition name for each error code is the same as the > phrase shown in the table, with underscores substituted for spaces. > For example, code 22012, DIVISION BY ZERO, has condition name > DIVISION_BY_ZERO. Condition names can be written in either upper or > lower case. > (Note that PL/pgSQL does not recognize warning, as opposed to error, > condition names; those are classes 00, 01, and 02.)" That means pl/pgsql will not recognize error codes under classes 00, 01, 02. Is there a way, I can output error code? exception when ... then when others then raise notice '%, %', SQLSTATE, SQLERRM; But it seems that SQLERRM and SQLSTATE did not work for me. By the way, I am using postgresql 8.0.1. Thanks a lot, Ying > <snip>
>On Thursday 16 March 2006 19:32, Emi Lu wrote: >|> Errors I got are: >|> syntax error at or near "SQLSTATE" at character 2613 >|> LINE 58: RAISE NOTICE 'Hmmmm.... [%,%]', SQLSTATE, SQL... >|> >|> >|> Should I install any patches or do anything elese to have SQLSTATE and >|> SQLERRM work for me? > > >I'm so sorry, i had a terrible day and in the rush to reply to you, i forgot >to mention the patch i applied some time ago. > >The patch and thread talking about it can be found here: > >http://archives.postgresql.org/pgsql-patches/2005-04/msg00123.php > >This was what i used in my 8.0.6, and it worked fine ever since. :) > > Thanks a lot Pedro. Could you help me how to apply this patch such as the steps to load the patch please? By the way, I am using postgresql 8.0.1. I think the patch will work for all 8.0.x version, right? Thanks again, Ying
Emi Lu <emilu@encs.concordia.ca> writes:
> Should I install any patches or do anything elese to have SQLSTATE and
> SQLERRM work for me?
Update to 8.1 ...
regards, tom lane
Emi Lu wrote: > >> <snip> From the page: >> "The PL/pgSQL condition name for each error code is the same as the >> phrase shown in the table, with underscores substituted for spaces. >> For example, code 22012, DIVISION BY ZERO, has condition name >> DIVISION_BY_ZERO. Condition names can be written in either upper or >> lower case. > >> (Note that PL/pgSQL does not recognize warning, as opposed to error, >> condition names; those are classes 00, 01, and 02.)" > > That means pl/pgsql will not recognize error codes under classes 00, 01, > 02. Correct - they are informational rather than errors. I'm not sure what it would mean to trap "successful completion" for example. > Is there a way, I can output error code? > exception > when ... then > when others then > raise notice '%, %', SQLSTATE, SQLERRM; > > But it seems that SQLERRM and SQLSTATE did not work for me. By the way, > I am using postgresql 8.0.1. http://www.postgresql.org/docs/8.1/static/release-8-1.html See section E.4.3.9 - they were defined in 8.1 -- Richard Huxton Archonet Ltd