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

From Mauricio Mantilla
Subject Re: help plpgsql, cursors, fetch into
Date
Msg-id 000e01c663f4$fadfaa50$988b76c8@unal40dd49d431
Whole thread Raw
In response to help plpgsql, cursors, fetch into  (Mauricio Mantilla <mauriciomantilla@cable.net.co>)
List pgsql-general
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

pgsql-general by date:

Previous
From: John F Rizzo
Date:
Subject: plperl on AIX
Next
From: "Clodoaldo Pinto"
Date:
Subject: Calling the same function more than once with the same arguments