Re: NO DATA FOUND Exception - Mailing list pgsql-sql

[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 INSELECT *FROM <table>WHERE prefix = v_searchLOOPreturn 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