PL/pgSQL存储过程报错42601：语法错误排查与解决

问题描述 #

执行创建内部表的PL/pgSQL存储过程时触发以下错误：

[42601] ERROR: syntax error at or near "$3" in context "|| $2 AS $3", at line 1
Where: PL/pgSQL function "create_internal_tables" line 19 at SQL statement

尝试将v_tgt_tab和v_src_tab声明为VARCHAR(50)、改用CONCAT函数后，错误依然存在。原存储过程代码如下：

CREATE OR REPLACE PROCEDURE create_internal_tables()
AS $$
DECLARE
    v_tgt_tab TEXT;
    v_src_tab TEXT;
    CNT INT = 0;
    wh_tab_rec RECORD;
BEGIN
FOR wh_tab_rec IN
            (
                SELECT  COALESCE(a.schema_name, b.schema_name) as schema_name,
                        COALESCE(a.table_name, b.table_name) as table_name
                FROM svv_all_tables AS a RIGHT OUTER JOIN svv_all_tables b
                ON  a.schema_name = b.schema_name AND
                    a.table_name = b.table_name
                WHERE   a.database_name = 'nonprod'
                AND     b.database_name = 'warehouse'
                ORDER BY b.schema_name, b.table_name
            )
    LOOP
       SELECT 'nonprod.' || wh_tab_rec.schema_name || '.tmp_' || wh_tab_rec.table_name AS v_tgt_tab;
       SELECT 'warehouse.' || wh_tab_rec.schema_name || '.' || wh_tab_rec.table_name AS v_src_tab;
        RAISE INFO 'Creating destination internal table %', v_tgt_tab;
        EXECUTE ('CREATE TABLE %I AS SELECT * FROM %I WHERE 1=0', v_tgt_tab, v_src_tab );
        CNT := CNT + 1;
    END LOOP;
    RAISE NOTICE '% internal tables created in destination.', CNT;
END;
$$ LANGUAGE plpgsql;

CALL create_internal_tables();

错误原因 #

1. EXECUTE语句用法错误：PostgreSQL中使用%I这类格式化占位符时，参数需要通过USING子句传递，而非直接作为EXECUTE的第二个参数。原写法会被解析成错误的SQL语法，导致$3相关的语法报错。
2. 变量赋值方式错误：原代码用SELECT ... AS v_tgt_tab无法给变量赋值，这种写法只会返回结果集，不会更新变量值。

解决后的代码 #

通过拆分存储过程、修正变量赋值方式，并添加系统模式过滤条件，问题得以解决：

CREATE OR REPLACE PROCEDURE create_table_as_select(
    target_table_name IN VARCHAR,
    source_table_name IN VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
EXECUTE 'CREATE TABLE ' || target_table_name || ' AS SELECT * FROM ' || source_table_name || ';';
END;
$$;


CREATE OR REPLACE PROCEDURE create_internal_tables()
LANGUAGE plpgsql
AS $$
DECLARE
    v_tgt_tab VARCHAR(256);
    v_src_tab VARCHAR(256);
    CNT INT = 0;
    wh_tab_rec RECORD;
BEGIN
FOR wh_tab_rec IN
            (
                SELECT  COALESCE(a.schema_name, b.schema_name) as schema_name,
                        COALESCE(a.table_name, b.table_name) as table_name
                FROM svv_all_tables AS a RIGHT OUTER JOIN svv_all_tables b
                ON  a.schema_name = b.schema_name AND
                    a.table_name = b.table_name
                WHERE   a.database_name = 'nonprod'
                AND     b.database_name = 'warehouse'
                AND     b.schema_name NOT LIKE 'pg_%'
                AND     b.schema_name NOT LIKE 'dbt_%'
                AND     b.schema_name NOT LIKE 'models%'
                AND     b.schema_name NOT IN ('information_schema', 'control')
                ORDER BY b.schema_name, b.table_name
            )
    LOOP
       v_tgt_tab := 'nonprod.' || wh_tab_rec.schema_name || '.tmp_' || wh_tab_rec.table_name;
       v_src_tab := 'warehouse.' || wh_tab_rec.schema_name || '.' || wh_tab_rec.table_name;
        --EXECUTE ('CREATE TABLE %I AS SELECT * FROM %I WHERE 1=0', v_tgt_tab, v_src_tab );
        CALL create_table_as_select(v_tgt_tab, v_src_tab);
        CNT := CNT + 1;
    END LOOP;
END;
$$;

CALL create_internal_tables();

关键修改点 #

• 用赋值操作符:=替代SELECT ... AS，正确给变量赋值
• 将表创建逻辑拆分为独立存储过程create_table_as_select，简化主逻辑并避免原EXECUTE的语法问题
• 添加了系统模式过滤条件，跳过不需要处理的系统表和特定业务模式

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