SQL Server转PostgreSQL动态查询语句转换求助

正在将数据库从SQL Server迁移至PostgreSQL，卡在一段动态查询语句的转换上。以下是原SQL Server语句（未声明的@variables为.NET变量）：

declare @table_name as varchar(64) 
set @table_name = (select tbl_name from tbl1 where id = @id)

if @table_name = 'xyz' 
begin
    exec('select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and condition2='+@vbl2+' and condition3=''abc'' and condition4='''+@vbl4+'''
          union all
          select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and isnull(col3,''0'')=''0'' and condition3=''abc'' and condition4='''+@vbl4+'''
         ')
end
else
begin
    exec('select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and condition2='+@vbl2+' and condition3=''abc''
          union all
          select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and isnull(col3,''0'')=''0'' and condition3=''abc''
         ')
end

尝试的转换代码存在语法错误，以下是修正后的正确实现：

关键错误修正点 #

• 函数参数冗余：无需传入V_tbl_name，应通过V_id从tbl1查询获取表名
• 字符串拼接风险：直接拼接变量易引发SQL注入和语法错误，改用PostgreSQL的format()函数安全处理标识符和常量
• 函数替换：PostgreSQL不支持isnull()，替换为标准SQL函数coalesce()
• 变量绑定：使用USING子句绑定参数，避免类型转换问题，提升安全性
• 返回类型明确：定义清晰的返回表结构，避免anyelement带来的类型歧义

正确转换代码 #

create function ufn_function1(V_id integer, vbl1 integer, vbl2 integer, vbl4 integer) 
returns table(col1 integer, col2 integer, col3 integer, col4 integer) as $$
declare
    V_table_name varchar := (select tbl_name from tbl1 where id = V_id);
begin
    if V_table_name = 'xyz' then
        return query execute format('
            select col1, col2, col3, col4 
            from %I 
            where condition1 = $1 and condition2 = $2 and condition3 = %L and condition4 = $3
            union all
            select col1, col2, col3, col4 
            from %I 
            where condition1 = $1 and coalesce(col3, %L) = %L and condition3 = %L and condition4 = $3
        ', V_table_name, 'abc', V_table_name, '0', '0', 'abc')
        using vbl1, vbl2, vbl4;
    else
        return query execute format('
            select col1, col2, col3, col4 
            from %I 
            where condition1 = $1 and condition2 = $2 and condition3 = %L
            union all
            select col1, col2, col3, col4 
            from %I 
            where condition1 = $1 and coalesce(col3, %L) = %L and condition3 = %L
        ', V_table_name, 'abc', V_table_name, '0', '0', 'abc')
        using vbl1, vbl2;
    end if;
end;
$$ language plpgsql;

代码说明 #

• %I：将表名作为SQL标识符处理，自动处理特殊字符、大小写和引号转义
• %L：将字符串转换为合法的SQL常量（自动添加单引号并转义内部引号）
• $1, $2：对应USING子句中的变量，按顺序绑定，避免SQL注入风险
• coalesce(col3, '0')：与SQL Server的isnull(col3, '0')功能完全一致，为标准SQL函数
• 返回类型returns table(...)：明确指定返回列的名称和数据类型，调用时无需额外定义类型（请根据实际列类型调整integer为对应类型，如varchar(50)等）

调用示例 #

-- 调用函数，直接获取结构化结果
select * from ufn_function1(1, 100, 200, 300);

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