NO DATA FOUND Exception

From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: NO DATA FOUND Exception
Date: 2007-06-25 19:20:37
Message-ID: 04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice pgsql-sql

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2007-06-25 19:35:21 Re: NO DATA FOUND Exception
Previous Message Michael Glaesemann 2007-06-25 19:07:03 Re: Move a database from one server to other

Browse pgsql-novice by date

  From Date Subject
Next Message Andrew Sullivan 2007-06-25 19:35:21 Re: NO DATA FOUND Exception
Previous Message Erik Jones 2007-06-25 19:03:58 Re: yet another simple SQL question

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Sullivan 2007-06-25 19:35:21 Re: NO DATA FOUND Exception
Previous Message Erik Jones 2007-06-25 19:03:58 Re: yet another simple SQL question