postgresql存储过程中quote_literal函数的使用

ouzhoudijie

由于业务需要，在写一个存储过程的时候，碰到一个问题折腾了比较长的时间才解决，希望大家在碰到类似的问题后不再浪费时间。【场景回顾】
大家看下面这个存储过程，编译是能通过的，但是调用它执行的时候返回false，这说明该过程抛异常了。
CREATE OR REPLACE FUNCTION fun_appid_insert_test(_start integer, _end integer)
RETURNS boolean AS
$BODY$
DECLARE
v_app_name character varying; -- 应用名称
v_id numeric;  -- 主键ID
v_index numeric;  -- 序号
v_sql character varying; -- 需要执行的sql
v_num numeric; -- 记录条数
BEGIN
v_index = 0;
for _start in _start.._end
loop   
-- 查询app_id是否分配过
select count(1) into v_num from appid_test where app_id = _start;
-- 若app_id没有分配，执行插入语句，注意，这里使用等号
if (v_num = 0) then
-- 获取序列的值作为主键ID
select nextval('seq_appid_test') into v_id;   
-- app_name的值都是pic+数字
v_app_name = 'pic' || v_index;
-- 拼接需要执行的插入语句            
v_sql = 'insert into appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' || v_app_name || ')';
Execute v_sql;
end if;
-- 序号自增1，app_name名称需要
v_index = v_index + 1;  
end loop;
RETURN true;
EXCEPTION
when others then
return false ;   
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

【问题分析】
经过排除法进行定位，问题可以锁定到这一行：v_sql = 'insert into xwq_appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' || v_app_name || ')';
将 v_id ，_start都换成常量后还是抛异常，那么问题应该出现在v_app_name这里，果然将v_app_name 换成常量数字字符串后，该过程能正常执行，注意是：常量数字字符串，
若换成中文汉字或者其它非数字类型字符串，过程还是不能执行，那么这里是不是需要转换一下呢？顺着这个思路，在网上查了一些资料，找到一个函数 quote_literal()，资料蛮少，
找到一博主对它的解释是：在字符串的前后加单引号，于是就拿来试试吧，于是将v_sql 的值改为： 'insert into xwq_appid_test(id, app_id, app_name) values(' || v_id || ',' || _start || ',' ||quote_literal(v_app_name) || ')';
然后编译并调用且执行该过程，返回值为true，成功了！
【存储过程最终版本】
CREATE OR REPLACE FUNCTION fun_appid_insert(_start integer, _end integer)
RETURNS boolean AS
$BODY$
DECLARE
v_app_name character varying; -- 应用名称
v_id numeric;  -- 主键ID
v_index numeric;  -- 序号
v_sql character varying; -- 需要执行的sql
v_num numeric; -- 记录条数
BEGIN
v_index = 0;
for _start in _start.._end
loop   
-- 查询app_id是否分配过
select count(1) into v_num from apply_appid_mng where app_id = _start;
-- 若app_id没有分配，执行插入语句，注意，这里使用等号
if (v_num = 0) then
-- 获取序列的值作为主键ID
select nextval('seq_apply_appid_mng') into v_id;   
-- app_name的值都是pic+数字
v_app_name = 'pic' || v_index;
-- 拼接需要执行的插入语句            
v_sql = 'insert into apply_appid_mng(id, app_id,app_name,status,approve_status,is_free,dept_name,first_level_type, app_type, run_type,
is_balance, online_type,app_desc, modify_time) values('|| v_id || ',' || _start || ',' || quote_literal(v_app_name) || ',3 ,1' || ',' || quote_literal('是')
|| ',' || quote_literal('多媒体事业部') || ',' || quote_literal('MVPN') || ',' || quote_literal('图片') || ',' || quote_literal('WAP') || ','
|| quote_literal('是') || ',' || quote_literal('非联网') || ',' || quote_literal('多媒体事业部图片资源') || ',' || quote_literal('2012-6-1 12:00:00.000')  ||')';
Execute v_sql;
end if;
-- 若app_id已经分配,将状态置为删除
Execute 'update appid_mng set status = 1 where app_id = ' || _start;   
-- 序号自增1，app_name名称需要
v_index = v_index + 1;  
end loop;
RETURN true;
EXCEPTION
when others then
return false ;   
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

【思考】
      如果quote_literal函数真如那位博所述，仅仅是在字符串前后加单引号的话，那么我换成非数字类型的字符串为什么就不行了呢？
【补充】
      postgresql官方说明文档对quote_literal的说明：返回给出字串的一个适用于在sql语句字串里当做文本使用的形式。嵌入的引号和反斜杠被恰当地写了双份。如quote_literal('0\'Reilly')，结果是'0''Reilly'。quote_literal一般动态sql中用的比较多，在拼接sql的时候，需要对sql语句字串内部的引号进行转义。     
      之所以数字类型的字符串不需要转义，我想是因为数据库中有一个自动类型转换的问题。     
      这样的话，所有的疑问都解释得通了，希望对大家有帮助。