From skarthi98@hotmail.com Tue Apr 3 22:35:27 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 6BEDE9FB8D9; Tue, 3 Apr 2007 19:35:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 13660-01; Tue, 3 Apr 2007 19:35:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc3-s29.bay0.hotmail.com (bay0-omc3-s29.bay0.hotmail.com [65.54.246.229]) by postgresql.org (Postfix) with ESMTP id D0C5F9FB452; Tue, 3 Apr 2007 19:35:18 -0300 (ADT) Received: from BAY131-W11 ([65.55.136.46]) by bay0-omc3-s29.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Tue, 3 Apr 2007 15:35:17 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_9f3191c3-5d5f-4037-96f2-5f5261a80110_" X-Originating-IP: [67.116.31.14] From: Karthikeyan Sundaram To: , Subject: exception handling in postgres plpgsql Date: Tue, 3 Apr 2007 15:35:17 -0700 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 03 Apr 2007 22:35:17.0430 (UTC) FILETIME=[5AA81D60:01C77640] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.502 tagged_above=0 required=5 tests=AWL=-0.792, BAYES_05=-1.11, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, HTML_40_50=0.496, HTML_MESSAGE=0.001, SPF_PASS=-0.001 X-Spam-Level: X-Archive-Number: 200704/25 X-Sequence-Number: 25089 --_9f3191c3-5d5f-4037-96f2-5f5261a80110_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 I am having a function like this =20 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_cod= ec audio_format.audio_codec%type, in p_mimetype audio_format.mimetype%ty= pe, 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 = =3D p_bitrate and sampling_rate =3D p_sampling_rate and mimetype = =3D p_mimetype and number_of_channel =3D p_mono_stero and audio_c= ode =3D p_audio_codec; return 1; exception when NO_DATA_FOUND then return 100;end;$$l= anguage 'plpgsql'; =20 When I compile, I am getting an error message=20 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? =20 Please advise. =20 Regards skarthi =20 _________________________________________________________________ i'm making a difference.=A0Make every IM count for the cause of your choice= . Join Now. http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=3Dhttp://= im.live.com/messenger/im/home/?source=3Dwlmailtagline= --_9f3191c3-5d5f-4037-96f2-5f5261a80110_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,

I am having a function like this

create or replace function audio_format_func (
    in p_b= itrate audio_format.audio_bitrate%TYPE,
    in p_sampling= _rate audio_format.sampling_rate%type,
    in p_bit_per_s= ample audio_format.bit_per_sample%type,
    in p_audio_co= dec audio_format.audio_codec%type,
    in p_mimetype audi= o_format.mimetype%type,
    in p_mono_stero audio_format.= number_of_channel%type) returns int as
$$
DECLARE
p_audio_i= d audio_format.audio_id%type;
begin
   select audio_id into a
     from = audio_format
    where audio_bitrate =3D p_bitrate
&n= bsp;     and sampling_rate =3D p_sampling_rate
= ;     and mimetype =3D p_mimetype
   =    and number_of_channel =3D p_mono_stero
   &n= bsp; and audio_code =3D p_audio_codec;
   return 1;
exception
   when NO_DATA_FOU= ND
   then
      return 100;
en= d;
$$
language 'plpgsql';

When I compile, I am getting an error message
ERROR: unrecognized exception condition "no_data_found"
CONTEXT:&n= bsp; 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.=A0Make every IM count for th= e cause of your choice. Join Now. = --_9f3191c3-5d5f-4037-96f2-5f5261a80110_-- From dev@freedomcircle.net Tue Apr 3 23:31:02 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id AE1E59FB9C4; Tue, 3 Apr 2007 20:31:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 10957-07; Tue, 3 Apr 2007 20:30:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by postgresql.org (Postfix) with ESMTP id 60B319FB995; Tue, 3 Apr 2007 20:30:59 -0300 (ADT) Received: from [192.168.7.5] ([71.101.9.227]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JFY00HRH3ZE3AG0@vms042.mailsrvcs.net>; Tue, 03 Apr 2007 18:30:52 -0500 (CDT) Date: Tue, 03 Apr 2007 19:30:43 -0400 From: Joe Subject: Re: [SQL] exception handling in postgres plpgsql In-reply-to: To: Karthikeyan Sundaram Cc: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Message-id: <1175643043.756.1.camel@pampa> Organization: Freedom Circle, LLC MIME-version: 1.0 X-Mailer: Evolution 2.10.0 FreeBSD GNOME Team Port Content-type: text/plain Content-transfer-encoding: 7bit References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.205 tagged_above=0 required=5 tests=AWL=-0.396, BAYES_00=-2.599, NO_DNS_FOR_FROM=3.2 X-Spam-Level: X-Archive-Number: 200704/26 X-Sequence-Number: 25090 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 From tgl@sss.pgh.pa.us Wed Apr 4 00:24:17 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CFCC79FB99B; Tue, 3 Apr 2007 21:24:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03444-03; Tue, 3 Apr 2007 21:24:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 742099FB98B; Tue, 3 Apr 2007 21:24:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l340OC20014031; Tue, 3 Apr 2007 20:24:12 -0400 (EDT) To: Karthikeyan Sundaram cc: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] exception handling in postgres plpgsql In-reply-to: References: Comments: In-reply-to Karthikeyan Sundaram message dated "Tue, 03 Apr 2007 15:35:17 -0700" Date: Tue, 03 Apr 2007 20:24:12 -0400 Message-ID: <14030.1175646252@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/27 X-Sequence-Number: 25091 Karthikeyan Sundaram 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 From ineyman@perceptron.com Wed Apr 4 13:18:05 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id EDE7A9FB6FE; Wed, 4 Apr 2007 10:18:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 03327-02; Wed, 4 Apr 2007 10:17:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from EXCHANGE.perceptron.com (exchange.perceptron.com [68.22.13.203]) by postgresql.org (Postfix) with ESMTP id 8167F9FB6D9; Wed, 4 Apr 2007 10:17:53 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C776BB.A58E02B3" Subject: Re: exception handling in postgres plpgsql Date: Wed, 4 Apr 2007 09:17:50 -0400 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] exception handling in postgres plpgsql Thread-Index: Acd2WyLwzDtoHPx9Rxa39iDEQO5nMQAYEo0Q References: From: "Igor Neyman" To: "Karthikeyan Sundaram" , , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/28 X-Sequence-Number: 25092 This is a multi-part message in MIME format. ------_=_NextPart_001_01C776BB.A58E02B3 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Obviously, you are coming from "Oracle world. In PG according to: =20 http://www.postgresql.org/docs/current/static/errcodes-appendix.html =20 exception WHEN no_data THEN ... =20 Igor ________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Karthikeyan Sundaram Sent: Tuesday, April 03, 2007 6:35 PM To: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org Subject: [ADMIN] exception handling in postgres plpgsql Hi, =20 I am having a function like this =20 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=20 where audio_bitrate =3D p_bitrate and sampling_rate =3D p_sampling_rate and mimetype =3D p_mimetype and number_of_channel =3D p_mono_stero and audio_code =3D p_audio_codec; return 1; exception when NO_DATA_FOUND then return 100; end; $$ language 'plpgsql'; =20 When I compile, I am getting an error message=20 ERROR: unrecognized exception condition "no_data_found" CONTEXT: compile of PL/pgSQL function "audio_format_func" near line 15 =20 How will I handle exceptions in postgres? =20 Please advise. =20 Regards skarthi =20 ________________________________ i'm making a difference. Make every IM count for the cause of your choice. Join Now. =20 ------_=_NextPart_001_01C776BB.A58E02B3 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

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

exception WHEN no_data THEN ...

Igor

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

Hi,

I am having a function like=20 this

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

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

How will I handle = exceptions in=20 postgres?

Please=20 advise.

Regards
skarthi

i'm making a difference. Make every IM count for the cause of your = choice.=20 Join=20 Now. ------_=_NextPart_001_01C776BB.A58E02B3--