Reputation: 868
I have the following code (a function which calls another one).Turns out in my second function the 'INSERT INTO' line tries to insert a row full of nulls.Why is this happening?
CREATE OR REPLACE FUNCTION LIMPIA_REPETIDOS()
RETURNS VOID AS $$
DECLARE
REP RECORD;
cursor1 CURSOR FOR SELECT DISTINCT usuario, fecha_hora_ret, tiempo_uso FROM auxi ORDER BY tiempo_uso DESC;
begin
open cursor1;
LOOP
FETCH cursor1 INTO REP;
EXIT WHEN NOT FOUND;
PERFORM GUARDA(REP.usuario, REP.fecha_hora_ret);
END LOOP;
CLOSE cursor1;
end;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION GUARDA
(myid auxi.usuario%TYPE, my_time auxi.fecha_hora_ret%type) RETURNS VOID AS $$
DECLARE
mycursor CURSOR FOR
SELECT * FROM auxi
WHERE myid = usuario AND my_time = fecha_hora_ret
ORDER BY tiempo_uso;
CANT INT;
mystruct RECORD;
BEGIN
OPEN mycursor;
CANT = 0;
LOOP
FETCH mycursor INTO mystruct;
EXIT WHEN NOT FOUND;
IF CANT = 2 THEN
INSERT INTO RECORRIDO_FINAL VALUES(mystruct.periodo, mystruct.usuario, mystruct.fecha_hora_ret, mystruct.est_origen, mystruct.est_origen, crear_fecha_hora_devolucion(mystruct.tiempo_uso, mystruct.fecha_hora_ret));
END IF;
CANT := CANT + 1;
END LOOP;
IF CANT = 1 THEN
INSERT INTO RECORRIDO_FINAL VALUES(mystruct.periodo, mystruct.usuario, mystruct.fecha_hora_ret, mystruct.est_origen, mystruct.est_destino, crear_fecha_hora_devolucion(mystruct.tiempo_uso, mystruct.fecha_hora_ret) );
END IF;
CLOSE mycursor;
END;
$$ LANGUAGE PLPGSQL;
What I'm trying to do here is to select rows from a table called 'auxi' and then transfer them to another one. The thing is when I have two or more rows in 'auxi' with the same values for 'usuario' and 'fecha_hora_ret' I have to sort those by 'tiempo_uso' and then select the second one.
Upvotes: 1
Views: 58
Reputation: 52118
When EXIT WHEN NOT FOUND gets executed the variable mystruct
will not contain any value since you have already gone through all the rows in the cursor.
So when you exit the loop for the case that the cursor contained 1 row only the INSERT will get executed with a mystruct
that is empty.
Upvotes: 1