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
Message-ID: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <37E566AC-D304-4D52-920B-7FA61D7777FD@myemma.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar
Subject: Re: NO DATA FOUND Exception
In-Reply-To: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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