Condition and loop within a function

Question

Condition and loop within a function

Navigation

#1 by (0 votes)

0

I am creating a function to output inventory, 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 if v_saldo_item_peps returned some result and go through these lines giving the outputs, until you have reached the total and there is balance in stock in the saldo_atual_individual column.

Ps. The current code works, but if the Qtd entered in the parameters is greater than the available quantity, it is negative, and if there is no row returned by the function, the QtyTable fields and values are null. What can not happen.

I put it in SQLFiddle to help: link

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'm just testing, it's not my final code, disregarding nomenclature / order and type of parameters / etc.

sql postgresql pl-sql

asked by anonymous 19.06.2017 / 16:30

1 answer

Excel stops working when I use workbook.close to close another worksheet Add column to SQL considering some conditions to be valid

score 0 · Accepted Answer

Resolved:

My problem was to know the syntax for the function.

language was set to sql , so it would not support conditional and loop. I had to change to plpgsql , and it did not support temporary tables. Then a cursor was set to store the result of the v_saldo_item_peps function.

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

Finally my role was this:

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 may be helpful to someone in the future.