From: | Mauricio Mantilla <mauriciomantilla(at)cable(dot)net(dot)co> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | help plpgsql, cursors, fetch into |
Date: | 2006-04-19 15:39:16 |
Message-ID: | 002d01c663c7$6a85e780$988b76c8@unal40dd49d431 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 144 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2006-04-19 15:42:39 | Re: How to implement a "subordinate database"? |
Previous Message | Brendan Duddridge | 2006-04-19 15:29:18 | Re: page is uninitialized? |