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.