help plpgsql, cursors, fetch into

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

Responses

Browse pgsql-general by date

  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?