cannot insert an record into a table inside a LOOP statement with pgplsql (Redshift environment)

Question

I cannot insert an record into a table inside a LOOP statement with PL/pgSQL (Redshift environment); everything works except adding the insert statement; and the procedure cannot be created/modified with the insert statement inside the LOOP logic.

The compiling error is

SQL Error \[42601\]: ERROR: syntax error at or near "$1"
Where: SQL statement in PL/PgSQL function "usp_ppv_process_cs_6months" near line 29

I tried to modify the procedure by adding the insert statement, but it just does not work; and a SQL Error 42601 appeared when trying to compile the procedure code.

create or replace  PROCEDURE aigdm.usp_ppv_process_cs_6months() AS $$
DECLARE
    po_nbr varchar(50);
    po_line_nbr varchar(50);
    due_date timestamp; 
    po_qty int; 
    po_amt_in_po_currency float8;
    rowcount int;
    target record; 
    open_po_cursor cursor for 
    SELECT
    wo_nbr
    ,po_line_nbr
    ,txn_date
    ,txn_qty
    ,po_amt_in_po_currency
    from
    aigdm.vw_aig_inventory_mrp
    where wo_nbr in ('257360CS','254358CS' );
BEGIN
    OPEN open_po_cursor; 
    po_nbr:= null;
    po_line_nbr := null;
    due_date := null; 
    po_qty := null; 
    po_amt_in_po_currency := null; 
     LOOP
        fetch open_po_cursor into po_nbr,po_line_nbr,due_date,po_qty,po_amt_in_po_currency;
        exit when not found;
        insert into aigdm.aig_bi_ppv_cs_6months (po_nbr) values (po_nbr); 
        commit;
    RAISE INFO 'a % at ', po_nbr; 
    END LOOP; 
    CLOSE open_po_cursor; 
END;
$$ LANGUAGE plpgsql;

Answer 1

The problem is that you use variable names that are equal to column names. Since you have a variable po_nbr, your INSERT statement

insert into aigdm.aig_bi_ppv_cs_6months (po_nbr) values (po_nbr)

leads to this prepared statement

insert into aigdm.aig_bi_ppv_cs_6months ($1) values ($1)

which is syntactically incorrect, since you cannot use a parameter for a column name.

Renaming the variable will fix the problem.

But the use of a procedure is quite unnecessary here. You could do the same thing much more efficiently with a single INSERT statement:

INSERT INTO aigdm.aig_bi_ppv_cs_6months (po_nbr)
SELECT wo_nbr
FROM aigdm.vw_aig_inventory_mrp
WHERE wo_nbr IN ('257360CS','254358CS' );