pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout how to use "exception when ??? then "
7+ messages / 3 participants
[nested] [flat]
* About how to use "exception when ??? then "
@ 2006-03-16 18:14 Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:01 ` Re: About how to use "exception when ??? then " Richard Huxton <dev@archonet.com>
2006-03-16 19:32 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 7+ messages in thread
From: Emi Lu @ 2006-03-16 18:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
@ 2006-03-16 19:01 ` Richard Huxton <dev@archonet.com>
2006-03-16 19:44 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2006-03-16 19:01 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:01 ` Re: About how to use "exception when ??? then " Richard Huxton <dev@archonet.com>
@ 2006-03-16 19:44 ` Emi Lu <emilu@encs.concordia.ca>
2006-03-17 08:11 ` Re: About how to use "exception when ??? then " Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Emi Lu @ 2006-03-16 19:44 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
> <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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:01 ` Re: About how to use "exception when ??? then " Richard Huxton <dev@archonet.com>
2006-03-16 19:44 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
@ 2006-03-17 08:11 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2006-03-17 08:11 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
@ 2006-03-16 19:32 ` Emi Lu <emilu@encs.concordia.ca>
2006-03-16 20:11 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 20:18 ` Re: About how to use "exception when ??? then " Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 7+ messages in thread
From: Emi Lu @ 2006-03-16 19:32 UTC (permalink / raw)
To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:32 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
@ 2006-03-16 20:11 ` Emi Lu <emilu@encs.concordia.ca>
1 sibling, 0 replies; 7+ messages in thread
From: Emi Lu @ 2006-03-16 20:11 UTC (permalink / raw)
To: Pedro B. <pedro.borracha@netcabo.pt>; +Cc: pgsql-sql
>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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: About how to use "exception when ??? then "
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:32 ` Re: About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
@ 2006-03-16 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-03-16 20:18 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Pedro B. <pedro.borracha@netcabo.pt>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-03-17 08:11 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-16 18:14 About how to use "exception when ??? then " Emi Lu <emilu@encs.concordia.ca>
2006-03-16 19:01 ` Richard Huxton <dev@archonet.com>
2006-03-16 19:44 ` Emi Lu <emilu@encs.concordia.ca>
2006-03-17 08:11 ` Richard Huxton <dev@archonet.com>
2006-03-16 19:32 ` Emi Lu <emilu@encs.concordia.ca>
2006-03-16 20:11 ` Emi Lu <emilu@encs.concordia.ca>
2006-03-16 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox