From: | Mladen Gogala <mgogala(at)vmsinfo(dot)com> |
---|---|
To: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | WHEN NO_DATA_FOUND THEN.... |
Date: | 2010-10-05 17:15:27 |
Message-ID: | 4CAB5D2F.2070801@vmsinfo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have a properly working function that looks like this:
create or replace function get_lang_id(p_lname varchar(150)) returns
int as
$$
DECLARE
p_lang_id int;
BEGIN
select lang_id into p_lang_id
from languages
where language=p_lname;
if (p_lang_id is null) then
p_lang_id=0;
end if;
return(p_lang_id);
END;
$$
LANGUAGE plpgsql;
Why am I writing this post? Well, it seems strange to me that the query
select lang_id into p_lang_id
from languages
where language=p_lname;
will quietly return NULL if the data is not found. Is that behavior
compliant with the standard? I must confess being used to the code like
this:
1 create or replace function get_lang_id(p_lname varchar2)
2 return integer as
3 p_lang_id integer;
4 begin
5 select lang_id into p_lang_id
6 from languages
7 where language=p_lname;
8 return(p_lang_id);
9 exception
10 when NO_DATA_FOUND then
11 return(0);
12* end;
SQL> /
Function created.
Elapsed: 00:00:00.38
SQL> select get_lang_id('Martian') from dual;
GET_LANG_ID('MARTIAN')
----------------------
0
Elapsed: 00:00:00.10
Is there any way for Postgres to raise an exception when no data is
found? I really like exceptions, they make it possible for me to handle
all of the errors in one place, without those pesky "if" clauses. Just
to make sure that I am understood, Oracle's behavior is even worse,
without the exception handler it will quietly return NULL, without any
errors.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-10-05 17:40:53 | Re: WHEN NO_DATA_FOUND THEN.... |
Previous Message | Mladen Gogala | 2010-10-05 12:30:38 | Re: Incremental Backup |