在Amazon Redshift创建PL/pgSQL存储过程时遇42601语法错误求助

错误信息 #

[42601] ERROR: syntax error at or near "$1" in context "256), $1", at line 1
Where: SQL statement in PL/PgSQL function "get_db_comparison_results" near line 28

用户编写的存储过程代码 #

CREATE OR REPLACE PROCEDURE get_db_comparison_results()
LANGUAGE plpgsql
AS $$
DECLARE
    wh_curr_tab RECORD;
    np_table_name VARCHAR(256);
BEGIN

drop table if exists db_comparison_results;
create table db_comparison_results (
        cmp_result VARCHAR(256),
    --nonprod column metadata
        np_schema_name VARCHAR(256),
        np_table_name VARCHAR(256),
        np_column_name VARCHAR(256),
        np_ordinal_position INT,
        np_data_type VARCHAR(256),
        np_character_maximum_length INT,
        np_numeric_precision INT,
        np_numeric_scale INT,
        np_is_nullable VARCHAR(256),
    --warehouse column metadata
        w_schema_name VARCHAR(256),
        w_table_name VARCHAR(256),
        w_column_name VARCHAR(256),
        w_ordinal_position INT,
        w_data_type VARCHAR(256),
        w_character_maximum_length INT,
        w_numeric_precision INT,
        w_numeric_scale INT,
        w_is_nullable VARCHAR(256)
    );



FOR wh_curr_tab IN (
    --for each warehouse table
    select * from information_schema.tables
    where table_type = 'BASE TABLE'
    and table_catalog = 'nonprod'
    and table_schema NOT LIKE 'pg_%'
    and table_schema NOT LIKE 'dbt_%'
    and table_schema NOT LIKE 'models%'
    and table_schema NOT IN ('information_schema', 'control')
    and table_name LIKE 'tmp_%'
)
LOOP
    np_table_name := split_part(wh_curr_tab.table_name,'_',2);
    if exists ( --nonprod table exists?
        select 1
        from information_schema.tables t
        where t.table_name = np_table_name
        and t.table_schema = wh_curr_tab.table_schema
        and t.table_catalog = 'nonprod'
                )
    then --yes, nonprod table exists, do schema compare

            insert into db_comparison_results
            select
                   CASE
                        WHEN cn.column_name IS NULL THEN 'Column does not exist in Nonprod'
                        WHEN cw.column_name IS NULL THEN 'Column does not exist in Warehouse'
                        WHEN cw.ordinal_position <> cn.ordinal_position THEN 'Column ordinal position mismatch'
                        WHEN cw.character_maximum_length <> cn.character_maximum_length THEN
                        'Column max length mismatch'
                        WHEN LOWER(cw.data_type) <> LOWER(cn.data_type) THEN
                        'Column data type mismatch'
                        WHEN COALESCE(cw.numeric_precision,0) <> COALESCE(cn.numeric_precision,0) THEN
                        'Column real numeric precision mismatch'
                        WHEN COALESCE(cw.numeric_scale,0) <> COALESCE(cn.numeric_scale,0) THEN
                        'Column real numeric scale mismatch'
                        WHEN cw.is_nullable <> cn.is_nullable THEN
                        'Column nullability criteria mismatch'
                        ELSE 'Columns match'
                   END,
                    cn.table_schema,
                    cn.table_name,
                    cn.column_name,
                    cn.ordinal_position,
                    cn.data_type,
                    cn.character_maximum_length,
                    cn.numeric_precision,
                    cn.numeric_scale,
                    cn.is_nullable,
                    cw.table_schema,
                    cw.table_name,
                    cw.column_name,
                    cw.ordinal_position,
                    cw.data_type,
                    cw.character_maximum_length,
                    cw.numeric_precision,
                    cw.numeric_scale,
                    cw.is_nullable
                from information_schema.columns cw
                full outer join information_schema.columns cn
                on  cw.table_schema = cn.table_schema
                and cw.table_name = concat('tmp_', cn.table_name)
                and cw.column_name = cn.column_name
                where cw.table_schema = wh_curr_tab.table_schema
                and cw.table_name = wh_curr_tab.table_name;

    else  --no, nonprod table does not exist,

        insert into db_comparison_results
        select 'Table does not exist in nonprod',
               wh_curr_tab.table_schema,
                np_table_name,
               coalesce(null,''),
               coalesce(null,-1),
               coalesce(null,''),
               coalesce(null,-1),
               coalesce(null,-1),
               coalesce(null,-1),
               coalesce(null,'');

    end if;

END LOOP;
END;
$$;

错误原因分析 #

这个错误是Redshift PL/pgSQL的特性限制导致：当你在静态SQL语句中直接引用记录类型变量（如wh_curr_tab）的字段时，Redshift的解析器无法正确处理这种变量替换，会生成无效的$1参数占位符，进而触发语法错误。

具体来说，循环中的EXISTS查询和INSERT语句直接使用了wh_curr_tab.table_schema、wh_curr_tab.table_name这类记录字段，这在Redshift PL/pgSQL中不被支持。

修复方案 #

1. 引入局部变量存储记录字段值 #

在循环内部，先把记录类型变量的字段值赋值给普通局部变量，再在SQL语句中使用这些变量：

CREATE OR REPLACE PROCEDURE get_db_comparison_results()
LANGUAGE plpgsql
AS $$
DECLARE
    wh_curr_tab RECORD;
    np_table_name VARCHAR(256);
    -- 新增局部变量存储schema和table名称
    curr_schema VARCHAR(256);
    curr_table VARCHAR(256);
BEGIN

drop table if exists db_comparison_results;
create table db_comparison_results (
        cmp_result VARCHAR(256),
    --nonprod column metadata
        np_schema_name VARCHAR(256),
        np_table_name VARCHAR(256),
        np_column_name VARCHAR(256),
        np_ordinal_position INT,
        np_data_type VARCHAR(256),
        np_character_maximum_length INT,
        np_numeric_precision INT,
        np_numeric_scale INT,
        np_is_nullable VARCHAR(256),
    --warehouse column metadata
        w_schema_name VARCHAR(256),
        w_table_name VARCHAR(256),
        w_column_name VARCHAR(256),
        w_ordinal_position INT,
        w_data_type VARCHAR(256),
        w_character_maximum_length INT,
        w_numeric_precision INT,
        w_numeric_scale INT,
        w_is_nullable VARCHAR(256)
    );

FOR wh_curr_tab IN (
    select * from information_schema.tables
    where table_type = 'BASE TABLE'
    and table_catalog = 'nonprod'
    and table_schema NOT LIKE 'pg_%'
    and table_schema NOT LIKE 'dbt_%'
    and table_schema NOT LIKE 'models%'
    and table_schema NOT IN ('information_schema', 'control')
    and table_name LIKE 'tmp_%'
)
LOOP
    -- 将记录字段赋值给局部变量
    curr_schema := wh_curr_tab.table_schema;
    curr_table := wh_curr_tab.table_name;
    np_table_name := split_part(curr_table,'_',2);
    
    if exists (
        select 1
        from information_schema.tables t
        where t.table_name = np_table_name
        and t.table_schema = curr_schema
        and t.table_catalog = 'nonprod'
                )
    then
            insert into db_comparison_results
            select
                   CASE
                        WHEN cn.column_name IS NULL THEN 'Column does not exist in Nonprod'
                        WHEN cw.column_name IS NULL THEN 'Column does not exist in Warehouse'
                        WHEN cw.ordinal_position <> cn.ordinal_position THEN 'Column ordinal position mismatch'
                        WHEN cw.character_maximum_length <> cn.character_maximum_length THEN
                        'Column max length mismatch'
                        WHEN LOWER(cw.data_type) <> LOWER(cn.data_type) THEN
                        'Column data type mismatch'
                        WHEN COALESCE(cw.numeric_precision,0) <> COALESCE(cn.numeric_precision,0) THEN
                        'Column real numeric precision mismatch'
                        WHEN COALESCE(cw.numeric_scale,0) <> COALESCE(cn.numeric_scale,0) THEN
                        'Column real numeric scale mismatch'
                        WHEN cw.is_nullable <> cn.is_nullable THEN
                        'Column nullability criteria mismatch'
                        ELSE 'Columns match'
                   END,
                    cn.table_schema,
                    cn.table_name,
                    cn.column_name,
                    cn.ordinal_position,
                    cn.data_type,
                    cn.character_maximum_length,
                    cn.numeric_precision,
                    cn.numeric_scale,
                    cn.is_nullable,
                    cw.table_schema,
                    cw.table_name,
                    cw.column_name,
                    cw.ordinal_position,
                    cw.data_type,
                    cw.character_maximum_length,
                    cw.numeric_precision,
                    cw.numeric_scale,
                    cw.is_nullable
                from information_schema.columns cw
                full outer join information_schema.columns cn
                on  cw.table_schema = cn.table_schema
                and cw.table_name = concat('tmp_', cn.table_name)
                and cw.column_name = cn.column_name
                where cw.table_schema = curr_schema
                and cw.table_name = curr_table;
    else
        -- 修复INSERT字段数量不匹配问题
        insert into db_comparison_results
        select 
            'Table does not exist in nonprod',
            curr_schema,
            np_table_name,
            coalesce(null,''),
            coalesce(null,-1),
            coalesce(null,''),
            coalesce(null,-1),
            coalesce(null,-1),
            coalesce(null,-1),
            coalesce(null,''),
            -- 补充warehouse部分的字段值
            curr_schema,
            curr_table,
            coalesce(null,''),
            coalesce(null,-1),
            coalesce(null,''),
            coalesce(null,-1),
            coalesce(null,-1),
            coalesce(null,-1),
            coalesce(null,'');
    end if;

END LOOP;
END;
$$;

2. 额外修复：字段数量不匹配问题 #

原代码中ELSE分支的INSERT语句只提供了10个字段值，但目标表db_comparison_results有19个字段，会导致执行时出现字段数量不匹配的错误，需要补充剩余字段的默认值（如NULL或空字符串）。

总结 #

• 核心错误是Redshift PL/pgSQL不支持在静态SQL中直接引用记录类型变量的字段，需要通过局部变量中转
• 同时修复字段数量不匹配问题，确保INSERT语句的字段数与目标表一致

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