Re: exception handling in postgres plpgsql

Obviously, you are coming from "Oracle world. In PG according to:

http://www.postgresql.org/docs/current/static/errcodes-appendix.html

exception WHEN no_data THEN ...

Igor

________________________________

From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Karthikeyan
Sundaram
Sent: Tuesday, April 03, 2007 6:35 PM
To: pgsql-admin(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
Subject: [ADMIN] exception handling in postgres plpgsql

Hi,

I am having a function like this

create or replace function audio_format_func (
in p_bitrate audio_format.audio_bitrate%TYPE,
in p_sampling_rate audio_format.sampling_rate%type,
in p_bit_per_sample audio_format.bit_per_sample%type,
in p_audio_codec audio_format.audio_codec%type,
in p_mimetype audio_format.mimetype%type,
in p_mono_stero audio_format.number_of_channel%type) returns int as
$$
DECLARE
p_audio_id audio_format.audio_id%type;
begin
select audio_id into a
from audio_format
where audio_bitrate = p_bitrate
and sampling_rate = p_sampling_rate
and mimetype = p_mimetype
and number_of_channel = p_mono_stero
and audio_code = p_audio_codec;
return 1;
exception
when NO_DATA_FOUND
then
return 100;
end;
$$
language 'plpgsql';

When I compile, I am getting an error message
ERROR: unrecognized exception condition "no_data_found"
CONTEXT: compile of PL/pgSQL function "audio_format_func" near line 15

How will I handle exceptions in postgres?

Please advise.

Regards
skarthi

________________________________

i'm making a difference. Make every IM count for the cause of your
choice. Join Now.
<http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=http:
//im.live.com/messenger/im/home/?source=wlmailtagline>