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?