PostgreSQL- If data in table, delete for stored procedure

Question

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;

Kadet · Accepted Answer · 2022-05-10T17:22:11.657

1

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

edited May 10 '22 at 17:22

answered May 10 '22 at 17:09

Kadet

1,344
3
10

Hi, I want that always that I call this procedure, if there is data: delete and then insert. If no data, just insert. Could you explain me where should I put this code? Just a small preview of how the code would look like :) Thanks!!! – Jose Antonio Piedehierro Arias May 10 '22 at 17:10
`truncate` is fastest way to empty table, no need to do additional check if you always want to delete all data. – Kadet May 10 '22 at 17:12
Thanks Grzegorz, I just need to put the truncate before the insert into in the loop?? – Jose Antonio Piedehierro Arias May 10 '22 at 17:15
Yes. Additionally you can replace loop with `Insert into ... select from` construction – Kadet May 10 '22 at 17:23
So, no need really to loop? Thx – Jose Antonio Piedehierro Arias May 10 '22 at 17:24
No need for loop. I am still not convinced that all data should be deleted if this procedure can be launched at the same moment for different periods by different people. – Kadet May 10 '22 at 17:29

PostgreSQL- If data in table, delete for stored procedure

1 Answers1