Postgres Pro
Downloads
Home   >   mailing lists

Thread: help plpgsql, cursors, fetch into

help plpgsql, cursors, fetch into

From
Mauricio Mantilla
Date:
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?

Re: help plpgsql, cursors, fetch into

From
Tom Lane
Date:
Mauricio Mantilla <mauriciomantilla@cable.net.co> writes:
> Hi, I'm having a problem with FETCH INTO, but I can't figure out what it is.

That kinda looks like a bug.  Which PG version are you using exactly?
Could you provide a self-contained test case (ie, a script to create
the needed tables and sample data)?

            regards, tom lane

Re: help plpgsql, cursors, fetch into

From
Mauricio Mantilla
Date:
Yes, it sure looks like a bug, this function used to work before, besides I
have another function that's pretty similar and it works.

Anyway, I did an script creating new tables and functions, and the function
worked.
I also found that in the original function i had another sentence which is
the one causing the problem...but only in the original function, in the new
function I created using the script, it doesn't cuase problems.

This is the original function:

CREATE OR REPLACE FUNCTION Asignar_test(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_test WHERE
id_solicitud=id;
    radio :=0.002;
    WHILE (SELECT id FROM taxi_test WHERE  posicion && Expand(punto,0.1) AND
Distance(punto,posicion) < radio AND  tipo_taxi like tipotax AND estado =
'l' LIMIT 1) is null AND radio < 1 LOOP
        radio := radio + 0.002;
    END LOOP;
     curs1 := cercanos_test(punto, radio, tipotax);
    i:=0;
    LOOP
        FETCH curs1 INTO taxi_id;
    EXIT WHEN NOT FOUND;
        i:=i+1;
        asignados[i] := taxi_id;
 UPDATE taxi SET estado = 'p' where id = taxi_id;
    END LOOP;
    CLOSE curs1;
    RETURN asignados;
END;
$$ LANGUAGE 'plpgsql';

The function works again when I delete the UPDATE sentence.


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mauricio Mantilla" <mauriciomantilla@cable.net.co>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, April 19, 2006 11:26 AM
Subject: Re: [GENERAL] help plpgsql, cursors, fetch into


> Mauricio Mantilla <mauriciomantilla@cable.net.co> writes:
>> Hi, I'm having a problem with FETCH INTO, but I can't figure out what it
>> is.
>
> That kinda looks like a bug.  Which PG version are you using exactly?
> Could you provide a self-contained test case (ie, a script to create
> the needed tables and sample data)?
>
> regards, tom lane
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 18/04/2006
>
>



--
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