Hi, I'm having a problem with FETCH INTO, but I can't figure out what it
is.
I have this function which works out well:
CREATE OR REPLACE FUNCTION Cercanos(punto
geometry,radio float, tipo varchar(1)) RETURNS refcursor AS
$$
DECLARE mycurs refcursor; BEGIN OPEN mycurs FOR SELECT id FROM taxi WHERE posicion && Expand(punto,100) AND Distance(punto,posicion) < radio AND tipo_taxi like tipo; RETURN mycurs; END; $$ LANGUAGE plpgsql; Then I call it inside this other function, this is the one I'm having
trouble with.
CREATE OR REPLACE FUNCTION Asignar(id_solicitud integer) RETURNS integer[] AS
$$
DECLARE curs1 refcursor; taxi_id numeric; punto geometry; radio float; asignados integer[]; tipotax varchar(1); i integer; BEGIN SELECT posicion,tipo INTO punto,tipotax FROM solicitud WHERE id_solicitud=id; radio :=0.002; WHILE (SELECT id FROM taxi WHERE estado = 'x' AND Distance(punto,posicion) < radio LIMIT 1) is null AND radio < 1 LOOP radio := radio + 0.002; END
LOOP;
RAISE NOTICE 'radio %', radio; curs1 := cercanos(punto, radio, tipotax); i:=0; LOOP FETCH curs1 INTO taxi_id; EXIT WHEN NOT FOUND; i:=i+1; asignados[i] := taxi_id; END LOOP; CLOSE curs1; RETURN asignados; END; $$ LANGUAGE 'plpgsql'; The function should return this array {1,3}
Instead it returns something like
{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3}
But when I run function Cercanos, I get 1 and 3 only one time, which is
what it should return cause column id from table taxi is unique.
I also tried to solve this by not letting the function return 1 more than
once in a row:
CREATE OR REPLACE FUNCTION Asignar(id_solicitud integer) RETURNS integer[] AS
$$ DECLARE curs1 refcursor; taxi_id numeric; punto geometry; radio float; asignados integer[]; tipotax varchar(1); i integer; BEGIN SELECT posicion,tipo INTO punto,tipotax FROM solicitud WHERE id_solicitud=id; radio :=0.002; WHILE (SELECT id FROM taxi WHERE estado = 'x' AND Distance(punto,posicion) < radio LIMIT 1) is null AND radio < 1 LOOP radio := radio + 0.002; END
LOOP;
RAISE NOTICE 'radio %', radio; curs1 := cercanos(punto, radio, tipotax); i:=0; LOOP FETCH curs1 INTO taxi_id; EXIT WHEN NOT FOUND; CONTINUE WHEN asignados[i] =
taxi_id;
i:=i+1; asignados[i] := taxi_id; END LOOP; CLOSE curs1; RETURN asignados; END; $$ LANGUAGE 'plpgsql'; Now the function returns something like this:
{1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3}
Does anyone know what micht be
wrong? |
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 18/04/2006