pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexception handling in postgres plpgsql
4+ messages / 4 participants
[nested] [flat]
* exception handling in postgres plpgsql
@ 2007-04-03 22:35 Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-03 23:30 ` Re: [SQL] exception handling in postgres plpgsql Joe <dev@freedomcircle.net>
2007-04-04 00:24 ` Re: [SQL] exception handling in postgres plpgsql Tom Lane <tgl@sss.pgh.pa.us>
2007-04-04 13:17 ` Re: exception handling in postgres plpgsql Igor Neyman <ineyman@perceptron.com>
0 siblings, 3 replies; 4+ messages in thread
From: Karthikeyan Sundaram @ 2007-04-03 22:35 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; pgsql-sql
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/ho...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] exception handling in postgres plpgsql
2007-04-03 22:35 exception handling in postgres plpgsql Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-03 23:30 ` Joe <dev@freedomcircle.net>
2 siblings, 0 replies; 4+ messages in thread
From: Joe @ 2007-04-03 23:30 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] exception handling in postgres plpgsql
2007-04-03 22:35 exception handling in postgres plpgsql Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-04 00:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-04-04 00:24 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: exception handling in postgres plpgsql
2007-04-03 22:35 exception handling in postgres plpgsql Karthikeyan Sundaram <skarthi98@hotmail.com>
@ 2007-04-04 13:17 ` Igor Neyman <ineyman@perceptron.com>
2 siblings, 0 replies; 4+ messages in thread
From: Igor Neyman @ 2007-04-04 13:17 UTC (permalink / raw)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>; pgsql-admin@postgresql.org; pgsql-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@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,
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-04-04 13:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 22:35 exception handling in postgres plpgsql Karthikeyan Sundaram <skarthi98@hotmail.com>
2007-04-03 23:30 ` Joe <dev@freedomcircle.net>
2007-04-04 00:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-04 13:17 ` Igor Neyman <ineyman@perceptron.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox