From: Michael Glaesemann <grzm@seespotcode.net>
To: Fernando Hevia <fhevia@ip-tel.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: NO DATA FOUND Exception
Date: Mon, 25 Jun 2007 15:05:19 -0500
Message-ID: <36BBC5B2-CF22-46C1-A20C-36342160B693@seespotcode.net> (raw)
In-Reply-To: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
References: <04e701c7b75d$e9e5c6e0$8f01010a@iptel.com.ar>
[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
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: grzm@seespotcode.net, fhevia@ip-tel.com.ar
Subject: Re: NO DATA FOUND Exception
In-Reply-To: <36BBC5B2-CF22-46C1-A20C-36342160B693@seespotcode.net>
* 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