WHEN NO_DATA_FOUND THEN.... - Mailing list 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