PostgreSQL- If data in table, delete for stored procedure-CodePudding

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.

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;

CodePudding user response：

If you always want to delete all data from table then just do always

truncate erp.tb_quarter;

place it in next line after BEGIN

If you want to delete only data from calculated range do delete with proper where

Additionaly in place of loop you can just use INSERT INTO ... SELECT FROM construction

Example

CodePudding user response：

Avoid a delete and insert. What that essentially is doing is an update. Depending on your data size, you can notice a large difference by doing one operation instead of two. Further, if you utilize the MERGE statement, you have a nice succinct, clear, and highly performant SQL statement.

https://www.postgresql.org/message-id/attachment/23520/sql-merge.html