help plpgsql, cursors, fetch into - Mailing list pgsql-general

From Mauricio Mantilla
Subject help plpgsql, cursors, fetch into
Date
Msg-id 002d01c663c7$6a85e780$988b76c8@unal40dd49d431
Whole thread Raw
Responses Re: help plpgsql, cursors, fetch into
List 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?

pgsql-general by date:

Previous
From: Brendan Duddridge
Date:
Subject: Re: page is uninitialized?
Next
From: Bruno Wolff III
Date:
Subject: Re: How to implement a "subordinate database"?