Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 35EDF9FB951
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 16:20:43 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 09985-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 16:20:39 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66])
	by postgresql.org (Postfix) with ESMTP id 3F6E29FB51D
	for <pgsql-sql@postgresql.org>; Mon, 25 Jun 2007 16:20:37 -0300 (ADT)
Received: from pcfhevia ([201.216.249.98])
	by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953
	for <pgsql-sql@postgresql.org>; Mon, 25 Jun 2007 16:20:36 -0300
From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: <pgsql-sql@postgresql.org>
Subject: NO DATA FOUND Exception
Date: Mon, 25 Jun 2007 16:20:37 -0300
Organization: IP-TEL
Message-ID: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
In-Reply-To: <37E566AC-D304-4D52-920B-7FA61D7777FD@myemma.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
Thread-Index: Ace3W7Rod/4ta7JuRMKtWf/2WXCM9gAAA6Mw
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/205
X-Sequence-Number: 28695

Hi.

Im taking my first steps with plpgsql.
I want my function to react to the result of a query in the following way:

begin
  select column into variable from table where condition;
exception
  when <<data_found>> then return variable;
  when <<no_data_found>> then <<do nothing/continue>> ;
  when <<any_other_exception>> then <<raise some error message>> ;
end ;

Is something like this possible en plpgsql without recurring to a select
count(*) to check how many results I will get?


Actual code is:

CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS
$body$
DECLARE
  v_len integer DEFAULT 8;
  v_search varchar;
  v_register num_geo%ROWTYPE;
BEGIN

  -- Search loop
  WHILE v_len > 0 LOOP
    v_search := substring(p_line, 1, v_len);
    begin
      SELECT * INTO v_register WHERE prefix = v_search;
    exception
	when no_data then		-- Getting error here
	    continue;
        when others then
	    return v_register.prefix;
    end;    
    v_len := v_len - 1;
  END LOOP;

  raise 'Not found';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE ;


ERROR: unrecognized exception condition "no_data"
SQL state: 42704
Context: compile of PL/pgSQL function "test" near line 14


Thanks,
Fernando.