我正在尝试在Amazon Redshift中创建这里所示的过程。但是，我反复收到这个错误：

[42601] 错误：在上下文 "256), $1" 附近的 $1处的语法错误，在第1 行 位置：PL/PgSQL函数 "get_db_comparison_results" 中的SQL语句，近第28行

我需要理解这个错误的含义。

这是PL/PgSQL的过程：

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;
$$;

解决方案

一些简单的故障排除就能准确定位错误到底在哪里。把建表的部分注释掉——错误会消失吗？如果会，会不会出现另外一个错误？如果会，那么错误就在建表这一步；如果没有，那就出现在循环或下面的代码中。这些都不需要你了解pl/pgsql或数据库知识。

然而，在这个特定的情况下，它相当清楚地告诉你错误出现在 "256), $1"，并提到第28行，也就是create-table语句的结尾。

于是，我们往下查看这些行，寻找在以256) 结束的某段之后的怪异之处，看到这个：

        np_schema_name VARCHAR(256),
        np_table_name VARCHAR(256),

那么，在你的变量声明中有什么？

DECLARE
    wh_curr_tab RECORD;
    np_table_name VARCHAR(256);

因此——就系统而言，你正试图在一个SQL语句中将该变量当作查询参数来使用。显然你并不打算这样做，但系统没法知道这一点。我建议你直接重命名该变量，看看是否能解决问题。