Skip site navigation (1) Skip section navigation (2)

Re: exception handling in postgres plpgsql

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Karthikeyan Sundaram" <skarthi98(at)hotmail(dot)com>,<pgsql-admin(at)postgresql(dot)org>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: exception handling in postgres plpgsql
Date: 2007-04-04 13:17:50
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A201C9649B@EXCHANGE.corp.perceptron.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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>  

In response to

pgsql-admin by date

Next:From: Damian LuboschDate: 2007-04-04 13:53:57
Subject: Hot Backup using WAL files
Previous:From: Tom LaneDate: 2007-04-04 00:24:12
Subject: Re: [SQL] exception handling in postgres plpgsql

pgsql-sql by date

Next:From: Gary StainburnDate: 2007-04-04 13:27:08
Subject: Solved - best way: diary functions.
Previous:From: Richard Broersma JrDate: 2007-04-04 13:06:45
Subject: Re: Moving a simple function to pl/pgsql (Novice question)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group