Create a procedure to fill a new table

Question

I am new to PostgreSQL and am struggling with the creation of a procedure to save the data in a new table.

This is how the empty table is created

CREATE TABLE erp.tb_quarter 
(
    quarter integer NOT NULL,
    year integer NOT NULL,
    cust_no character(5) NOT NULL,
    iva_percent integer NOT NULL,
    amount real NOT NULL,

    CONSTRAINT pk_quarter
        PRIMARY KEY (quarter, year),

    CONSTRAINT fk_quarter
        FOREIGN KEY (cust_no)       
        REFERENCES erp.tb_customer (cust_no)
);

The foreign key is associated with this table

CREATE TABLE tb_customer
(
    cust_no CHAR(5) NOT NULL,
    cust_name VARCHAR(50) NOT NULL,
    cust_cif VARCHAR (15) NOT NULL,
    last_updated_by VARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_tb_customer PRIMARY KEY (cust_no)
);

Other information can be extracted from this table:

CREATE TABLE erp.tb_invoice 
(
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
);

This is how the procedure looks like. I want the procedure pr_calc_quarter to calculate the year and quarter, and insert the data in the table tb_quarter of all the customers, grouped by the iva type (VAT) for each client.

CREATE OR REPLACE PROCEDURE pr_calc_quarter(integer, integer, character, integer, real) 
LANGUAGE 'plpgsql'      
AS $$

BEGIN
    SELECT
      extract(quarter from c.last_update_date) AS quarter,
      extract (year from c.last_update_date) AS year,
      c.cust_no,
      (i.iva_amount/i.tot_amount *100) AS iva_percent,
      i.tot_amount AS amount
        

    FROM
      erp.tb_customer AS c, 
      erp.tb_invoice AS i,
      erp.tb_quarter AS q
    
    WHERE
        i.cust_no = c.cust_no

    GROUP BY
       extract(quarter from c.last_update_date)
 

INSERT INTO erp.tb_quarter (quarter, year, cust_no, iva_percent, amount) VALUES ($1, $2, $3, $4, $5); 
COMMIT;

END;
$$;

I am not sure if in this case a for loop is required so that I can check in each line the invoice of the customer and then sum the total amounts for each type of iva (VAT).

Am I on the right way to get it?

Thanks.

Answer 1

CREATE OR REPLACE PROCEDURE pr_calc_quarter(input_quarter integer, input_year integer) 
LANGUAGE 'plpgsql'      
AS $$

BEGIN
INSERT INTO erp.tb_quarter (year, quarter, cust_no, iva_percent, amount) 
SELECT extract (year FROM i.last_update_date) AS anio,
       extract (quarter FROM i.last_update_date) AS quarter,
       i.cust_no, 
       ROUND(100*i.iva_amount/i.tot_amount) AS iva_percent,
       SUM(i.tot_amount) AS amount

    FROM erp.tb_invoice AS i
    WHERE extract (quarter FROM i.last_update_date)= input_quarter AND
        extract (year FROM i.last_update_date)= input_year
    GROUP BY i.cust_no,iva_percent, quarter, anio;
    
COMMIT;

END;
$$;