Error due to cancel sale in POSTGRESQL

0

Good afternoon, I'm implementing a sell rebate function that is as follows:

CREATE OR REPLACE FUNCTION estorna_venda(p_id_venda INTEGER) RETURNS VARCHAR 
AS
$BODY$
DECLARE v_id_prod INTEGER;
DECLARE v_prod_qtd INTEGER;
DECLARE v_contador INTEGER;
DECLARE v_qtd_estorno INTEGER;
BEGIN
v_contador := (SELECT count(venda_id) FROM ivenda WHERE venda_id = 
p_id_venda);

WHILE v_contador != 0 LOOP
    CREATE OR REPLACE VIEW produtos_estorno AS (SELECT * FROM ivenda WHERE 
venda_id = p_id_venda ORDER BY prod_id ASC LIMIT 1);  

    v_qtd_estorno := (SELECT qtde FROM produtos_estorno);
    v_id_prod := (SELECT prod_id FROM produtos_estorno);
    v_prod_qtd = (SELECT qtd_estoque FROM estoque WHERE id_produto = 
    v_id_prod);

    UPDATE estoque SET qtd_estoque = (v_qtd_estorno + v_prod_qtd) FROM 
    estoque WHERE id_produto = v_id_prod;   
    DELETE FROM ivenda WHERE venda_id = p_id_venda AND prod_id = v_id_prod;
    v_contador = (v_contador - 1);

END LOOP;
DELETE FROM ivenda WHERE venda_id = p_id_venda;
DELETE FROM venda WHERE id_venda = p_id_venda;
END;
$BODY$
LANGUAGE plpgsql;

And when I'm going to call the function like this

SELECT estorna_venda(1);

I get the following error:

  

ERROR: column "p_id_venda" does not exist   LINE 1: ... AS reversal (SELECT * FROM ivenda WHERE venda_id = p_id_venda ...                                                                ^   QUERY: CREATE OR REPLACE VIEW products_store AS (SELECT * FROM ivenda WHERE venda_id = p_id_venda ORDER BY prod_id ASC LIMIT 1)   CONTEXT: PL / pgSQL function estorna_venda (integer) line 10 at SQL statement   SQL state: 42703

I can not solve this problem. I would like help from someone experienced in the field, thank you.

    
asked by anonymous 18.05.2018 / 21:20

0 answers