Redshift PLpgSQL循环中的参数化列名

发布于12月08日

我需要关于如何使用SQL(或动态SQL)来编写循环的帮助，在该循环中我使用‘x’作为列名的占位符.有没有人有什么建议，让我不必为每一列手动运行这条语句？

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;

上面的代码是我所设想的，但它显示了一个错误

错误:语法错误位于或接近"$1"WHERE:PL/PgSQL函数"pci_Columns"中第5行附近的SQL语句[错误ID:1-6571f613-3df6ae50214fb89252a028fb])

因为我不认为我可以参数化这样的对象

推荐答案

使用动态SQL EXECUTE.不幸的是，RedShift doesn't有format()个(它是added in PostgreSQL 9.1，在RedShift从PostgreSQL 8.0.2派生出来之后的一段时间)，所以在将列名注入语句之前，您需要使用quote_ident()个列名.

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();