Re: NO DATA FOUND Exception

[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