Return result from a select inside a loop

Question

I’ve been trying for a few hours to get the result of a select that is within a pgsql function. I don’t have much experience with sql. I researched a little but I was not very successful. Follow my function for analysis:

    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()

That one into at the end of select returns only the last result for being inside a loop, but I need to return all the values of select. I tried to put resultado:= before select but ai returns syntax error.

Answer 1

Take a look at the RETURN NEXT command: https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html

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 from Function to SETOF VARCHAR.