Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 1D3509FB66A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 17:05:35 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 34791-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 25 Jun 2007 17:05:28 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mtao03.charter.net (mtao03.charter.net [209.225.8.188])
	by postgresql.org (Postfix) with ESMTP id 106609FB6C8
	for <pgsql-sql@postgresql.org>; Mon, 25 Jun 2007 17:05:30 -0300 (ADT)
Received: from aa03.charter.net ([10.20.200.155]) by mtao03.charter.net
	(InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP
	id <20070625200526.IDYB1450.mtao03.charter.net@aa03.charter.net>;
	Mon, 25 Jun 2007 16:05:26 -0400
Received: from [10.0.1.2] (really [66.188.164.45]) by aa03.charter.net
	with ESMTP id <20070625200525.ORWY7864.aa03.charter.net@[10.0.1.2]>;
	Mon, 25 Jun 2007 16:05:25 -0400
In-Reply-To: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
References: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <36BBC5B2-CF22-46C1-A20C-36342160B693@seespotcode.net>
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: NO DATA FOUND Exception
Date: Mon, 25 Jun 2007 15:05:19 -0500
To: "Fernando Hevia" <fhevia@ip-tel.com.ar>
X-Mailer: Apple Mail (2.752.2)
X-Chzlrs: 0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/208
X-Sequence-Number: 28698

[Please create a new message to post about a new topic, rather than  
replying to and changing the subject of a previous message. This will  
allow mail clients which understand the References: header to  
properly thread replies.]

On Jun 25, 2007, at 14:20 , Fernando Hevia wrote:

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

I think you want to look at FOUND.

http://www.postgresql.org/docs/8.2/interactive/plpgsql- 
statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

For example:

# select * from foos;
foo
-----
bar
baz
bat
(3 rows)

# CREATE FUNCTION foos_exist()
RETURNS boolean
LANGUAGE plpgsql AS $body$
DECLARE
         v_foo TEXT;
BEGIN
         SELECT INTO v_foo
                foo
         FROM foos;
         IF FOUND THEN RETURN TRUE;
         ELSE RETURN FALSE;
         END IF;
END;
$body$;
CREATE FUNCTION
# select foos_exist();
foos_exist
------------
t
(1 row)

# truncate foos;
TRUNCATE TABLE
test=# select foos_exist();
foos_exist
------------
f
(1 row)

> 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;

I think you might want to rewrite this using some of the information  
here:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- 
structures.html#PLPGSQL-RECORDS-ITERATING

For example, your inner loop could loop could look something like this:

FOR v_register IN
	SELECT *
	FROM <table>
	WHERE prefix = v_search
	LOOP
	return v_register.prefix;
END LOOP;

If no data is found, the loop won't do anything.

However, it looks like you're trying to return a set of results  
(i.e., many rows), rather than just a single row. You'll want to look  
at set returning functions. One approach (probably not the best)  
would be to expand p_line into all of the possible v_search items and  
append that to your query, which would look something like:

SELECT prefix
FROM
<table>
WHERE prefix IN (<list of v_search items>).

Another way to do this might be to not use a function at all, but a  
query along the lines of

SELECT prefix
FROM <table>
WHERE p_line LIKE prefix || '%';

Hope this helps.

Michael Glaesemann
grzm seespotcode net