I have this procedure, which I call with
CALL pr_calc_quarter(2,2022), that insert data into a table
erp.tb_quarter from a query.
I need to add code to check that if there is already data in the table, to delete it before inserting new values.
I was thinking of inserting the code at the beggining or before the insert into, but I don’t know
how to check if there is already data on the table.
I just need how to do the check, no need for working demo :). I don’t know if trigger before insert could work here with parameters in the procedure.
CREATE OR REPLACE PROCEDURE pr_calc_quarter( trimestre INT , anio INT ) AS $$ DECLARE v_cust_no CHARACTER(5); v_iva_percent INT; v_amount REAL; BEGIN FOR trimestre, anio, v_cust_no, v_iva_percent, v_amount IN SELECT EXTRACT(QUARTER FROM i.last_update_date) AS quarter, EXTRACT(year FROM i.last_update_date) AS year, c.cust_no, round((i.iva_amount * 100 / net_amount)::numeric,0) AS iva_percent, SUM(i.iva_amount) AS amount FROM erp.tb_customer c JOIN erp.tb_invoice i ON i.cust_no = c. cust_no WHERE EXTRACT(QUARTER FROM i.last_update_date) = trimestre AND EXTRACT(year FROM i.last_update_date) = anio GROUP BY iva_percent, c.cust_no, year, quarter ORDER BY c.cust_no, iva_percent, amount LOOP INSERT INTO erp.tb_quarter VALUES (trimestre, anio, v_cust_no, v_iva_percent, v_amount); END LOOP; END; $$ LANGUAGE plpgsql;
If you always want to delete all data from table then just do always
place it in next line after
If you want to delete only data from calculated range do
delete with proper
Additionaly in place of loop you can just use
INSERT INTO ... SELECT FROM construction