Condition and loop within a function

Question

I am creating a function to exit the stock, where v_saldo_item_peps is a function that returns a table, with the items that have balance, in their respective entries.

I need to check whether v_saldo_item_peps returned some result and go through these lines giving the outputs, while it has not reached the total and there is stock balance in the column saldo_atual_individual.

ps. The current code works, but if the Qtd reported in the parameters is greater than the available amount, it is negative, and if there is no line returned by the function, the Qtdatual fields and values are null. What must not happen.

I put in Sqlfiddle to help: http://sqlfiddle.com/#! 17/87a17/2

Function code:

CREATE OR REPLACE FUNCTION public.saida_estoque (  produto varchar,  estoque 

varchar,  lote varchar, qtd numeric)
RETURNS INTEGER  AS
$body$
with xAtual as (
SELECT
 codigo,
 custo_medio,
 data,
 estoque,
 lote,
 produto,
 qtd,
 valor,
 saldo_atual,
 saldo_atual_individual
FROM
v_saldo_item_peps($1,$2,$3))

--Aqui verificar se xAtual tem registros, caso contrário cancelar tudo.
--Percorrer cada linha, executando as saídas (`INSERTS`).
--Ex. qtd = 10, na primeira linha saldo_atual_individual = 5. 
--Dá saída em 5 unidades, pela primeira linha, vai para a próxima.
--Na segunda linha, saldo_atual_individual  = 6.
--Dá saída em 5, e fica 1 de saldo naquela entrada.
--Se possível, ao final, retornar um INTEGER[] com todos os códigos gerados nas sáidas `(INSERT)`.
--Se chegar na última linha e ainda não tiver saldo para completar, cancelar tudo.


INSERT INTO 
  public.estoque
(
  produto,
  lote,
  estoque,
  data,
  qtd,
  valor,
  saldo_anterior,
  saldo_atual,
  custo_medio,
  id_peps,
  saldo_ant_peps,
  saldo_atual_peps
) 
VALUES (
  $1,
  $3,
  $2,
  now(),
  $4,
  (SELECT x.valor from xAtual x),--valor saida
  (COALESCE((SELECT x.saldo_atual from estoque x where x.produto = $1 and x.lote = $3 and x.estoque = $2 order by x.codigo desc limit 1),0)),
  (coalesce((SELECT x.saldo_atual from estoque x where x.produto = $1 and x.lote = $3 and x.estoque = $2 order by x.codigo desc limit 1),0) + $4),
  (SELECT x.custo_medio from xAtual x),--preco medio
  (SELECT x.codigo from xAtual x)  ,--Codigo da entrada q deu saída
  (SELECT x.saldo_atual_individual from xAtual x),--saldo anterior
  (SELECT x.saldo_atual_individual from xAtual x) + $4 --Saldo Atual
) returning codigo;



$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Note: I am only doing tests, it is not my final code, disregard nomenclature / order and type of parameters / etc.

Answer 1

Solved:

My problem was knowing the syntax for the function.

To language was set to sql, thus would not support conditional and loop. I had to change to plpgsql, and this did not support the temporary tables. So a cursor to store the function result v_saldo_item_peps.

I changed the type of return to integer[] to return all the keys of the records entered during the process, and declared some variables to calculate the available balances / amount of movements.

That’s finally how my job is:

CREATE OR REPLACE FUNCTION public.saida_estoque (  produto varchar,  estoque varchar,  lote varchar, qtd numeric)
RETURNS INTEGER[]  AS
$body$
declare 

retorno integer[] DEFAULT '{}';
xaux integer; --variavel auxiliar resultado do insert
temptable CURSOR FOR Select * from v_saldo_item_peps($1,$2,$3); --tabela (cursor) temporaria entradas / estoque / saldo atual
qtdMov numeric; --variavel com a qtd de cada movimento inserido
qtdRestante numeric; --qtd restante a inserir nos movimentos
diferenca numeric; --qtd da diferenca entre saldo atual e movimento

BEGIN

qtdMov := $4;
qtdRestante := qtdMov;

FOR xAtual in  temptable LOOP

--xAtual.saldo_atual_individual --Saldo atual do item / entrada

diferenca := xAtual.saldo_atual_individual + qtdRestante;
IF (diferenca <0) then
qtdMov := qtdRestante - diferenca;
else
qtdMov := qtdRestante;
end if;

qtdRestante := qtdRestante - qtdMov;


INSERT INTO 
  public.estoque
(
  produto,
  lote,
  estoque,
  data,
  qtd,
  valor,
  saldo_anterior,
  saldo_atual,
  custo_medio,
  id_peps,
  saldo_ant_peps,
  saldo_atual_peps
) 
VALUES (
  $1,
  $3,
  $2,
  now(),
  qtdMov,
  (xAtual.valor),--valor saida
  (COALESCE((SELECT x.saldo_atual from estoque x where x.produto = $1 and x.lote = $3 and x.estoque = $2 order by x.codigo desc limit 1),0)),
  (coalesce((SELECT x.saldo_atual from estoque x where x.produto = $1 and x.lote = $3 and x.estoque = $2 order by x.codigo desc limit 1),0) + qtdMov),
  (xAtual.custo_medio),--preco medio
  (xAtual.codigo)  ,--ref
  (xAtual.saldo_atual_individual),--saldo anterior
  (xAtual.saldo_atual_individual) + qtdMov
) returning codigo into xaux;

  retorno := retorno || xaux ;

  if (qtdRestante = 0) THEN
     EXIT;
  END IF;


END LOOP;

if (qtdRestante != 0) then
     RAISE EXCEPTION 'Não há saldo do item.'; 
end if;

return retorno;

END;


$body$
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Although I have not had answers, I leave here my solution to the question so that it can be useful to someone in the future.