Create a procedure to get grouped by data from a table to another given a certain year and quarter in PostgreSql

Question

I am trying to get this code to work. I want to create a procedure that given a certain year and quarter it fills a table (tb_quarter) with grouped by information of another two tables.

So far I have this:

CREATE OR REPLACE PROCEDURE pr_calc_quarter 
(year tb_quarter.year%type, quarter tb_quarter.quarter%type) AS $$
BEGIN
    INSERT INTO tb_quarter
        SELECT
            EXTRACT(quarter FROM l.last_update_date) AS quarter,
            EXTRACT(year FROM l.last_update_date) AS year,
            cust_no,
            ROUND(l.iva_amount/l.net_amount*100) AS iva_percent,
            SUM(l.iva_amount) AS amount
        FROM 
            tb_lines l
        INNER JOIN
            tb_invoice i USING (invoice_id)
        GROUP BY
            cust_no,
            iva_percent,
            year,
            quarter;
END;
$$LANGUAGE plpgsql
;

So far, the procedure works to fill in the table, but without taking into account the year and quarter I put in.

I am unable to solve the code in order to select only the data that matches the year and the quarter.

I've tried this:

CREATE OR REPLACE PROCEDURE pr_calc_quarter 
    (year tb_quarter.year%type, quarter tb_quarter.quarter%type) AS $$
BEGIN
        SELECT
            EXTRACT(quarter FROM l.last_update_date) AS l_quarter,
            EXTRACT(year FROM l.last_update_date) AS l_year,
            cust_no,
            ROUND(l.iva_amount/l.net_amount*100) AS iva_percent,
            SUM(l.iva_amount) AS amount
        FROM 
            tb_lines l
        INNER JOIN
            tb_invoice i USING (invoice_id)
        WHERE l_quarter = quarter
            AND l_year = year
        GROUP BY
            cust_no,
            iva_percent,
            l_year,
            l_quarter;
        INSERT INTO tb_quarter VALUES (quarter,year,iva_percent,amount);
END;
$$LANGUAGE plpgsql
;

But it returns an error saying the column l_quarter doesn't exist. I've also tried to change the names of the input parameters (year + quarter), but it also returns error saying it is not allowed.

These are the tables I am working with:

CREATE TABLE 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_tb_quarter PRIMARY KEY(quarter, year, cust_no, iva_percent),
    CONSTRAINT  fk_tb_customer FOREIGN KEY (cust_no) REFERENCES tb_customer(cust_no)
);

CREATE TABLE erp.tb_lines (
invoice_id            INT NOT NULL,
line_id               INT  NOT NULL,
line_num              INT NOT NULL,
item                  CHARACTER(5),
description           CHARACTER VARYING(120)  NOT NULL,
net_amount            REAL NOT NULL,
iva_amount            REAL NOT NULL,
last_updated_by       CHARACTER VARYING(20) DEFAULT 'SYSTEM',
last_update_date      DATE NOT NULL,
CONSTRAINT pk_lines PRIMARY KEY (line_id),
CONSTRAINT fk_lines_invoice FOREIGN KEY (invoice_id) REFERENCES erp.tb_invoice (invoice_id)

);

 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)  

);

Finally I was able to solve it using nbk's answer and changing it a little bit:

    CREATE OR REPLACE PROCEDURE pr_calc_quarter
    (year tb_quarter.year%type, quarter tb_quarter.quarter%type) AS $$
BEGIN
    INSERT INTO tb_quarter
        SELECT
            EXTRACT(quarter FROM l.last_update_date) AS l_quarter,
            EXTRACT(year FROM l.last_update_date) AS l_year,
            cust_no,
            ROUND(l.iva_amount/l.net_amount*100) AS iva_percent,
            SUM(l.iva_amount) AS amount
        FROM 
            tb_lines l
        INNER JOIN
            tb_invoice i USING (invoice_id)
        WHERE 
            EXTRACT(quarter FROM l.last_update_date) = quarter
            AND EXTRACT(year FROM l.last_update_date) = year
        GROUP BY
            cust_no,
            iva_percent,
            l_year,
            l_quarter;
        
END;
$$LANGUAGE plpgsql
;

Answer 1

Postgres doesn't support using aliases anywhere, so you could simply repeat the extract

CREATE OR REPLACE PROCEDURE pr_calc_quarter 
(_year tb_quarter.year%type, _quarter tb_quarter.quarter%type) AS $$
BEGIN
    INSERT INTO tb_quarter
        SELECT
            EXTRACT(quarter FROM l.last_update_date) AS l_quarter,
            EXTRACT(year FROM l.last_update_date) AS l_year,
            cust_no,
            ROUND(l.iva_amount/l.net_amount*100) AS iva_percent,
            SUM(l.iva_amount) AS amount
        FROM 
            tb_lines l
        INNER JOIN
            tb_invoice i USING (invoice_id)
        WHERE 
        EXTRACT(quarter FROM l.last_update_date) = _quarter
               AND EXTRACT(year FROM l.last_update_date) = _year;
        GROUP BY
            cust_no,
            iva_percent,
            year,
            quarter
END;
$$LANGUAGE plpgsql;