Parameterize column names in Redshift PL/pgSQL loop

Question

I need help on how to use SQL (or dynamic SQL) to code out a loop where I am using 'x' as a placeholder for column names. Anyone have any suggestion so I don't have to run this statement manually for every column?

CREATE OR REPLACE PROCEDURE PCI_COLUMNS()
AS $$
DECLARE
  x RECORD;
BEGIN
    FOR x IN (select col from L_USA.COLUMNS WHERE TYPE = 'PCI')
    LOOP
        ATTACH MASKING POLICY COMMERCIAL_PCI
        ON L_USA.DEMO(x)
        USING (x, CUST_ID)
        TO ROLE COMM_PCI
        PRIORITY 30;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

The above code is what I was envisioning but it spits out an error

ERROR: syntax error at or near "$1" Where: SQL statement in PL/PgSQL function "pci_columns" near line 5 [ErrorId: 1-6571f613-3df6ae50214fb89252a028fb])

because I don't think I can parameterize objects like this

Answer 1

Use dynamic SQL EXECUTE. Unfortunately, Redshift doesn't have format() (it was added in PostgreSQL 9.1, some time after Redshift forked from PostgreSQL 8.0.2), so you need to quote_ident() your column name before injecting it into the statement.

CREATE OR REPLACE PROCEDURE PCI_COLUMNS()
AS $$
DECLARE x RECORD;
BEGIN
    FOR x IN (SELECT quote_ident(col) AS quoted_column 
              FROM L_USA.COLUMNS WHERE TYPE = 'PCI')
    LOOP EXECUTE 
        'ATTACH MASKING POLICY COMMERCIAL_PCI' ||
        'ON L_USA.DEMO('||x.quoted_column||')' ||
        'USING ('||x.quoted_column||', CUST_ID)' ||
        'TO ROLE COMM_PCI' ||
        'PRIORITY 30;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CALL PCI_COLUMNS();