Juan David Ospina Juan David Ospina - 7 months ago 16
SQL Question

PL/PGSQL Cursor for rows mod 3

So I am working in PL/PGSQL and I want to create a function that returns only the data from the rows that are row%3=0 and I need to use a cursor.

I have the following but it is obviously not working

create or replace function punto6() returns void AS $$
declare
rPers personas%ROWTYPE;
curs3 CURSOR IS select * from personas order by nombres;
BEGIN
FOR rPers IN curs3 LOOP
if current of curs3 % 3 =0 THEN
RAISE NOTICE ''Nombre: % %'', rPers.nombres,
rPers.apellidos;
END LOOP;
return;
end;
$$ LANGUAGE plpgsql;

Answer
create or replace function punto6() returns void AS $$
declare
    rPers RECORD;               
    curs3   CURSOR  IS  select * from personas order by nombres;
    numero int;
BEGIN
    numero = 1;
    OPEN curs3;
    LOOP
    FETCH curs3 INTO rPers;
    EXIT WHEN NOT FOUND;
    if numero % 3 = 0   THEN
        RAISE   NOTICE  'Nombre:    %   %', rPers.nombres,  
        rPers.apellidos;
    END IF;
    numero = numero + 1;
    END LOOP;
    CLOSE curs3;
    return;         
end;
$$  LANGUAGE plpgsql;
Comments