使用EXECUTE FORMAT编写动态SQL时无法引用CTE表列的问题求解

错误根因 #

你写法的核心逻辑错误是：FORMAT 函数的所有入参都在动态SQL语句执行前完成模板替换，此时你写的动态SQL内的CTE还未被解析运行，c.type 这类CTE运行时才会产生的列引用根本不存在，自然会报变量不存在的错误。你相当于把SQL运行阶段才能拿到的行级值，提前放到了预编译阶段的字符串模板参数里，逻辑本身不成立。

解决方案 #

把行级拼接的FORMAT写到动态SQL的SELECT字段列表内部，区分两层FORMAT的作用：

1. 外层FORMAT：负责拼接动态SQL模板，仅传入静态参数（比如你要替换的过滤值、动态指定的列名等）
2. 内层FORMAT：属于动态SQL运行时执行的逻辑，用来逐行读取CTE的列值完成拼接

固定拼接type列的正确写法 #

EXECUTE FORMAT ('WITH cte AS (SELECT *, case 
                        when var1 = %L then %L
                        when var2 = '''' then ''''
                        else '''' end, ''adding_dummy_text_column''
             FROM some_other_table sot
             WHERE sot.type = ''java'')

             INSERT INTO my_new_table 
                    SELECT *, FORMAT(''This is the new column I want to make with some data from my CTE %s'', c.type)
                    FROM cte c', 'word1', 'word2'
             );

动态指定拼接列的写法 #

如果你需要灵活修改要拼接的列，只需把列名作为字符串传给外层FORMAT的%I占位符即可：

EXECUTE FORMAT ('WITH cte AS (SELECT *, case 
                        when var1 = %L then %L
                        when var2 = '''' then ''''
                        else '''' end, ''adding_dummy_text_column''
             FROM some_other_table sot
             WHERE sot.type = ''java'')

             INSERT INTO my_new_table 
                    SELECT *, FORMAT(''This is the new column I want to make with some data from my CTE %s'', c.%I)
                    FROM cte c', 'word1', 'word2', 'type' -- 第三个参数传你要拼接的列名字符串
             );

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