创建Redshift存储过程遇[42601][500310]语法错误，求解决

我尝试创建以下Redshift存储过程，用于删除nidhi_scratch schema下的表，但保留指定临时表中的表（临时表数据来自nidhi_test.redshift_table_name，redshift_table_name由Airflow传入）：

CREATE or REPLACE PROCEDURE admin.sp_pm_drop_all_scratch_tables (redshift_table_name IN VARCHAR)
                   LANGUAGE plpgsql
                   as $$
                   DECLARE
                      temp_table_name varchar(100);
                      row record;
                   BEGIN
                      temp_table_name := 'procedure_temp_table';
                      EXECUTE 'DROP TABLE if exists '||temp_table_name;
                      EXECUTE 'CREATE TEMP TABLE '||temp_table_name||' as select * from  nidhi_test.'||redshift_table_name;
                      FOR row IN(
                       select 'DROP TABLE IF EXISTS '|| schemaname || '."'|| tablename|| '" cascade' as sql_name
                       from pg_tables
                         where schemaname in ('nidhi_scratch')
                         and schemaname || '.'|| tablename not in (select schema_name || '.'|| table_name  from temp_table_name)
                   )
                   LOOP
                   EXECUTE row.sql_name;
                   END LOOP;
                   end;
                   $$ ;

创建及执行该过程时均报错：[42601][500310] Amazon Invalid operation: syntax error at or near "$1";

错误原因分析 #

1. HTML实体误用：代码中使用了HTML转义字符"代替双引号，Redshift无法识别该字符，导致生成的DROP语句语法错误。
2. 静态SQL中引用变量作为表名：子查询select schema_name || '.'|| table_name from temp_table_name中，temp_table_name是PL/pgSQL变量，静态SQL会将其当作表名而非变量值，导致找不到名为temp_table_name的表，触发语法错误。
3. 不安全的字符串拼接：直接拼接输入参数redshift_table_name到EXECUTE语句中，存在SQL注入风险，且若表名包含特殊字符会引发语法错误。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE admin.sp_pm_drop_all_scratch_tables (redshift_table_name IN VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
    temp_table_name VARCHAR(100) := 'procedure_temp_table';
    drop_stmt TEXT;
BEGIN
    -- 清理已有临时表
    EXECUTE 'DROP TABLE IF EXISTS ' || temp_table_name;
    
    -- 安全创建临时表，使用USING传递参数避免SQL注入
    EXECUTE 'CREATE TEMP TABLE ' || temp_table_name || ' AS SELECT schema_name, table_name FROM nidhi_test.$1'
    USING redshift_table_name;
    
    -- 遍历需要删除的表，生成正确的DROP语句
    FOR drop_stmt IN
        SELECT 'DROP TABLE IF EXISTS "' || schemaname || '"."' || tablename || '" CASCADE' AS drop_stmt
        FROM pg_tables
        WHERE schemaname = 'nidhi_scratch'
          AND schemaname || '.' || tablename NOT IN (
              SELECT schema_name || '.' || table_name FROM procedure_temp_table
          )
    LOOP
        EXECUTE drop_stmt;
    END LOOP;
END;
$$;

关键修改说明 #

• 替换HTML实体：将"替换为直接的双引号"，并通过"包裹schema和表名，确保带特殊字符的表名能被正确识别。
• 直接引用临时表名：子查询中直接使用创建的临时表名procedure_temp_table，而非变量temp_table_name，避免静态SQL解析错误。
• 使用USING传递参数：创建临时表时，通过USING redshift_table_name传递输入参数，避免字符串拼接带来的SQL注入风险和语法错误。
• 优化变量声明：将temp_table_name的初始化直接放在声明部分，简化代码结构。
• 明确字段选择：创建临时表时明确指定schema_name, table_name字段，避免不必要的字段被带入，提升效率。

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