Re: BUG #2785: Exception Issue

The following bug has been logged online:

Bug reference: 2785
Logged by: Patrick Hayes
Email address: pkhayeskp(at)hotmail(dot)com
PostgreSQL version: 8.1
Operating system: Windows Professional
Description: Exception Issue
Details:

I am defining Exception blocks. When I put something that others in the
when clause, it will not compile. The others clause works fine.

The following is working fine.
BEGIN

PPSDATA.member_date_of_birth=date(substr(ROWDATA.inline,55,4)||'-'||
substr(ROWDATA.inline,59,2)||'-'||substr(ROWDATA.inline,61,2));
EXCEPTION WHEN OTHERS THEN
ERRORCODE:='Y';
PPSERROR.current_month_error:='*';
END;

The following is not working fine.
BEGIN
select count(*) into cnt_county from county_code
where county_code = PPSERROR.member_county_code;
EXCEPTION
WHEN no_data then
PPSERROR.member_county_code_error:='*';
RAISE NOTICE ' ---county_code_error out %', PPSERROR.member_county_code;
ERRORCODE:='Y';
END;

I get the following error.

postgres-# language plpgsql;
ERROR: unrecognized exception condition "no_data"
CONTEXT: compile of PL/pgSQL function "chip_pps_data_check" near line 101

Thanks

"Patrick Hayes" <pkhayeskp(at)hotmail(dot)com> writes:
> I get the following error.

> postgres-# language plpgsql;
> ERROR: unrecognized exception condition "no_data"
> CONTEXT: compile of PL/pgSQL function "chip_pps_data_check" near line 101

Indeed, because no_data is not an error condition, so EXCEPTION can't
catch it. Possibly you could do something with testing the FOUND
variable instead.

regards, tom lane

Tom,
Thank you for your input. I found a way to check for it by counting the
return from a count. Thanks again for your help.

PAT

>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>To: "Patrick Hayes" <pkhayeskp(at)hotmail(dot)com>
>CC: pgsql-bugs(at)postgresql(dot)org
>Subject: Re: [BUGS] BUG #2785: Exception Issue Date: Mon, 27 Nov 2006
>22:05:04 -0500
>
>"Patrick Hayes" <pkhayeskp(at)hotmail(dot)com> writes:
> > I get the following error.
>
> > postgres-# language plpgsql;
> > ERROR: unrecognized exception condition "no_data"
> > CONTEXT: compile of PL/pgSQL function "chip_pps_data_check" near line
>101
>
>Indeed, because no_data is not an error condition, so EXCEPTION can't
>catch it. Possibly you could do something with testing the FOUND
>variable instead.
>
> regards, tom lane

_________________________________________________________________
MSN Shopping has everything on your holiday list. Get expert picks by style,
age, and price. Try it!
http://shopping.msn.com/content/shp/?ctId=8000,ptnrid=176,ptnrdata=200601&tcode=wlmtagline