Postgres通过DB LINK执行动态查询时单引号转义问题求助

问题分析 #

你遇到的报错是因为动态拼接dblink的SQL语句时，没有正确处理字符串中的单引号。生成的SQL里，nsp.nspname = 'dwh_10'的单引号会和外层dblink参数的单引号冲突，导致语法解析错误。

解决方案 #

方法1：使用quote_literal()函数安全引用变量 #

quote_literal()会自动处理变量中的单引号转义，避免手动拼接出错。修改后的代码如下：

DO $$
DECLARE
sqlSmt text;
v_new_count NUMERIC:=0;
item record;

begin
 sqlSmt = null;

 FOR item IN
 (select nsp.nspname schema, cls.relkind obj_type from pg_class cls 
 join pg_roles rol on rol.oid = cls.relowner 
 join pg_namespace nsp on nsp.oid = cls.relnamespace
 where nsp.nspname like 'dwh%' 
 group by nsp.nspname, cls.relkind 
 order by nsp.nspname, cls.relkind limit 10)
 LOOP

 sqlSmt = 'select * from dblink(''old_live'', ''select count(*) from pg_class cls
 join pg_roles rol on rol.oid = cls.relowner
 join pg_namespace nsp on nsp.oid = cls.relnamespace
 where nsp.nspname = ' || quote_literal(item.schema) || ' and cls.relkind = ' || quote_literal(item.obj_type) || '') as total_count(total_count numeric)';

 EXECUTE sqlSmt INTO v_new_count;
 raise notice '%', sqlSmt;
 raise notice '%, %, %', item.schema, item.obj_type, v_new_count;

 END LOOP;

END $$;

方法2：使用美元引号（Dollar Quoting）简化单引号处理 #

美元引号可以避免嵌套单引号的转义麻烦，直接用自定义标记包裹字符串，更清晰安全。修改后的代码：

DO $$
DECLARE
sqlSmt text;
v_new_count NUMERIC:=0;
item record;

begin
 sqlSmt = null;

 FOR item IN
 (select nsp.nspname schema, cls.relkind obj_type from pg_class cls 
 join pg_roles rol on rol.oid = cls.relowner 
 join pg_namespace nsp on nsp.oid = cls.relnamespace
 where nsp.nspname like 'dwh%' 
 group by nsp.nspname, cls.relkind 
 order by nsp.nspname, cls.relkind limit 10)
 LOOP

 sqlSmt = format('select * from dblink(''old_live'', $q$select count(*) from pg_class cls
 join pg_roles rol on rol.oid = cls.relowner
 join pg_namespace nsp on nsp.oid = cls.relnamespace
 where nsp.nspname = %L and cls.relkind = %L$q$) as total_count(total_count numeric)', item.schema, item.obj_type);

 EXECUTE sqlSmt INTO v_new_count;
 raise notice '%', sqlSmt;
 raise notice '%, %, %', item.schema, item.obj_type, v_new_count;

 END LOOP;

END $$;

这里用format()函数配合%L占位符自动转义字符串，同时用$q$作为dblink内部SQL的美元引号标记，彻底避免单引号冲突问题。

说明 #

• quote_literal()会把变量转义成带单引号的字符串，适合手动拼接SQL时使用。
• format()函数的%L占位符等价于quote_literal()，结合美元引号可以让代码更易读，减少出错概率。
• 两种方法都能解决单引号转义的问题，推荐使用方法2，代码更简洁且维护性更高。

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