Redshift PL/pgSQL循环中参数化列名的实现方法求助

你遇到的核心问题是：PL/pgSQL不支持直接将变量作为数据库对象名（如表、列名）进行参数化，必须通过动态SQL构造完整的SQL语句后执行。

原代码及报错信息 #

原尝试的存储过程代码：

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;

执行时触发的错误：

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

解决方案：使用动态SQL + format()函数 #

通过EXECUTE执行动态构造的SQL语句，推荐用format()函数处理标识符拼接，它会自动处理标识符的引号转义，避免语法错误和SQL注入风险。修改后的代码如下：

CREATE OR REPLACE PROCEDURE PCI_COLUMNS()
AS $$
DECLARE
  col_name TEXT; -- 用TEXT类型存储列名，比RECORD更直观
BEGIN
    -- 遍历所有需要绑定策略的列
    FOR col_name IN (SELECT col FROM L_USA.COLUMNS WHERE TYPE = 'PCI')
    LOOP
        -- 动态构造并执行掩码策略绑定语句
        EXECUTE format(
            'ATTACH MASKING POLICY COMMERCIAL_PCI
             ON L_USA.DEMO(%I)
             USING (%I, CUST_ID)
             TO ROLE COMM_PCI
             PRIORITY 30;',
            col_name, col_name -- 两次传入列名，对应两个%I占位符
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

关键说明 #

1. %I占位符：format()中的%I会将传入的字符串作为SQL标识符处理，自动添加双引号（如果列名包含特殊字符或关键字），确保语法合法。
2. 变量类型：将原来的x RECORD改为col_name TEXT，因为查询结果只需要列名字段，更简洁。
3. 动态SQL执行：EXECUTE会运行构造好的SQL字符串，实现循环绑定掩码策略的需求。

注意事项 #

• 确保L_USA.COLUMNS表中col列存储的是L_USA.DEMO表中真实存在的列名，否则执行时会触发"列不存在"的错误。
• 执行该存储过程的角色需要具备ATTACH MASKING POLICY权限，以及对L_USA.DEMO表的操作权限。

内容的提问来源于stack exchange，提问作者user2712