Return result of a select that is inside a loop

0

I'm trying for a few hours to get the result of a select that is inside a function in pgsql. I do not have much experience with sql. I researched a little but did not get much success. Here's my function for review:

    create or replace function fimExpediente() returns varchar as $$
    declare 
        liga_antes record;
        new_select record;
        resultado varchar;
    begin
        for liga_antes in select bicolaborador, max(bidataevento) bidataevento
            from 
                tbbilhetes
            where 
                bicolaborador > 0 
            and 
                bidataevento > '12/01/2017' 
            and 
                bidataevento < '13/01/2017' 
            and 
                biignicao = 1 
            group by 
                bicolaborador
            loop
               select 
                   bidataevento 
               from tbbilhetes 
               where 
                   bidataevento >= liga_antes.bidataevento 
               and 
                   biignicao = 0 and bicolaborador = liga_antes.bicolaborador 
               order by 
                   bidataevento asc limit 1 
               into resultado;
         end loop;
         return resultado;

    end;
    $$ language 'plpgsql';

    select fimExpediente()

This into at the end of select returns only the last result because it is inside a loop, but I need to return all select values. I tried to put resultado:= before select but it returns syntax error.

    
asked by anonymous 23.01.2017 / 14:03

1 answer

0

Take a look at the RETURN NEXT command: link

You can call it from within the same loop as it just adds the line to the output.

Edit: You will have to change the return type of the function to SETOF VARCHAR.

    
23.01.2017 / 20:33