Thread: 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.

Hi,

On Tue, 2007-04-03 at 15:35 -0700, Karthikeyan Sundaram wrote:
>   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

The constant is no_data.  See
http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html

Joe

Karthikeyan Sundaram <skarthi98@hotmail.com> writes:
> When I compile, I am getting an error message
> ERROR:  unrecognized exception condition "no_data_found"CONTEXT:  compile o=
> f PL/pgSQL function "audio_format_func" near line 15
> =20
> How will I handle exceptions in postgres?

Reading between the lines I gather that you are reading 8.2
documentation and trying to apply the info to some previous version that
doesn't have SELECT INTO STRICT (which you failed to use anyway...)

You probably want to test the magic FOUND variable instead --- see the
plpgsql docs.

            regards, tom lane