PostgreSQL函数中连续Update异常：首次生效二次失效，手动执行正常

嘿，我来帮你搞定这个问题！你遇到的情况很典型——函数里第一条UPDATE正常生效，第二条却没动静，手动执行又没问题，大概率是动态SQL的语法转义出错或者不必要地使用动态SQL导致的。

问题根源分析 #

你用execute format()执行第二条UPDATE时，单引号的处理方式不符合PostgreSQL的语法规则。在PostgreSQL的字符串常量中，要表示单个单引号必须用两个单引号转义，但你写的split_part(per_num,''-'',1)会导致语法解析错误，这条UPDATE语句根本没正确执行，只是函数没抛出异常让你察觉而已。另外，其实你这个场景完全不需要用动态SQL，反而把事情复杂化了。

解决方案 #

方案1：直接使用普通UPDATE（推荐）

既然不需要动态生成表名、字段名这类变量，直接写普通UPDATE语句就行，彻底避免动态SQL的坑：

-- 第一条UPDATE保持不变
UPDATE edmonton.weekly_pmt_report 
SET permit_number = pmt.prnum 
FROM (
    SELECT permit_details, split_part(permit_details,'-',1) AS prnum 
    FROM edmonton.weekly_pmt_report
) pmt 
WHERE edmonton.weekly_pmt_report.permit_details = pmt.permit_details;

-- 第二条UPDATE改成普通写法，去掉execute format
UPDATE edmonton.weekly_pmt_report wpr
SET address = ds_dt.adr, 
    job_description = ds_dt.job, 
    applicant = ds_dt.apnt
FROM (
    SELECT split_part(per_num, '-', 1) AS job_id, 
           job_des AS job,
           addr AS adr, 
           applic AS apnt 
    FROM edmonton.descriptive_details
) ds_dt
WHERE wpr.permit_number = ds_dt.job_id;

方案2：如果一定要用动态SQL（规范写法）

如果确实需要动态SQL，用format()的占位符%L来自动处理字符串转义，这是PostgreSQL的最佳实践：

execute format('
    UPDATE edmonton.weekly_pmt_report wpr
    SET address = ds_dt.adr, 
        job_description = ds_dt.job, 
        applicant = ds_dt.apnt
    FROM (
        SELECT split_part(per_num, %L, 1) AS job_id, 
               job_des AS job,
               addr AS adr, 
               applic AS apnt 
        FROM edmonton.descriptive_details
    ) ds_dt
    WHERE wpr.permit_number = ds_dt.job_id
', '-');

这里%L会自动把'-'转义成符合SQL语法的字符串，避免手动转义出错。

额外排查技巧 #

为了避免以后再遇到类似“没报错但不生效”的问题，建议给函数添加异常处理，这样能直接看到执行时的错误信息：

BEGIN
    -- 两条UPDATE语句放在这里
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '执行出错：%', SQLERRM;
        RAISE; -- 重新抛出异常，方便定位问题
END;

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