PostgreSQL PL/pgSQL存储过程的CTE中如何注入动态表名变量

问题解决方案 #

首先明确一个误区：带CTE的动态查询完全可以在PL/pgSQL中正常执行，你之前出现语法报错和CTE本身无关，是动态SQL的拼接写法不正确导致的。

你遇到的tbl_nm无法识别的问题根源是：PL/pgSQL的静态SQL只会把变量解析为值，不会识别为表名、列名这类标识符，直接写from tbl_nm co时，PostgreSQL会把tbl_nm当成字面量表名去查找，而不是读取你传入的参数值，自然会报错找不到表。

以下是修正后的完整存储过程代码：

CREATE OR REPLACE PROCEDURE public.some_dumb_procedure(batch_size integer, tbl_nm text)
    LANGUAGE plpgsql
AS
$procedure$
begin
    EXECUTE format(
        'WITH my_dumb_CTE_list AS (
            select co.col1, co.col2, col_val
            from %I co
            where exists(
                select 1
                from crappy_table c
                where c.id = co.customer_ref_id
                  and co.offer_expiry_date < now() - interval ''180 days''
            )
            and not exists(
                select 1 from super_stupid_list ssl where ssl.tbl_nm = ''master'' and ssl.id = co.id
            )
            limit $1
        )
        INSERT INTO my_dumb_table
        select * from my_dumb_CTE_list',
        tbl_nm
    ) USING batch_size;
    commit;
end;
$procedure$;

注意事项 #

• format()函数中的%I是专门用来处理标识符的占位符，会自动给传入的表名转义、加引号，避免SQL注入风险
• SQL字符串里的单引号需要转义，用两个单引号''代替一个单引号
• batch_size这类值参数通过USING子句传入，对应SQL里的$1占位符，不需要拼到SQL字符串中，性能更高也更安全
• 你原来的调用语句参数数量不匹配：存储过程只定义了2个入参，你调用时传了3个参数，需要改成匹配的写法：

call some_dumb_procedure(50000, 'thetable');

如果确实需要第三个参数，要先在存储过程定义里补充对应的入参声明。

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