[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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
