CODEWITHSUNDEEP
sqlpostgresqlplpgsqldatabase-cursor
Flama
Flama

Reputation: 868

INSERT INTO inserts a row full of nulls

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

Answers (1)

Joakim Danielson
Joakim Danielson

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 mystructthat is empty.

Upvotes: 1

Related Questions