PostgreSQL存储过程无更新操作问题排查求助

问题描述 #

我编写了一段PostgreSQL存储过程，其中的UPDATE语句未执行任何更新操作，但将相同逻辑（仅指定单个部门值'05'）的UPDATE语句在存储过程外执行时却能正常工作。刚接触存储过程，难以定位问题原因，恳请帮忙分析。

不生效的存储过程代码 #

DECLARE
  departements TEXT[];
  dept TEXT;
BEGIN
    departements := ARRAY['05','06','07','08'];
FOREACH dept IN ARRAY departements LOOP
raise info '%', dept;
    UPDATE table AS m SET mycolumn = CAST(round(m.valeur * (1 + e.evolution / 100)) AS integer) FROM evolution AS e WHERE m.code_dept = dept AND m.annee = e.annee;
END LOOP;     
END;

可正常生效的语句（存储过程外） #

UPDATE table AS m SET mycolumn = CAST(round(m.valeur * (1 + e.evolution / 100)) AS integer) FROM evolution AS e WHERE m.code_dept = '05' AND m.annee = e.annee;

问题原因及解决办法 #

• 变量名冲突是核心问题
  存储过程中使用的循环变量dept，如果恰好和table表中的某个列名重名，PostgreSQL会把WHERE m.code_dept = dept解析为m.code_dept = m.dept（表内列与列的对比），而非引用循环变量dept，这就导致更新条件完全不符合预期，自然不会有数据被更新。

• 修复方案
  给循环变量起一个不会和表列名冲突的名字，比如加前缀v_，这也是PL/pgSQL的常用命名规范：

  DECLARE
    departements TEXT[];
    v_dept TEXT; -- 修改变量名避免冲突
  BEGIN
      departements := ARRAY['05','06','07','08'];
  FOREACH v_dept IN ARRAY departements LOOP
  raise info '%', v_dept;
      UPDATE table AS m SET mycolumn = CAST(round(m.valeur * (1 + e.evolution / 100)) AS integer) 
      FROM evolution AS e 
      WHERE m.code_dept = v_dept AND m.annee = e.annee; -- 引用新变量名
  END LOOP;     
  END;
  

• 额外优化建议
  没必要用循环逐个更新，直接用ANY操作符配合数组一次性完成更新，效率更高：

  UPDATE table AS m 
  SET mycolumn = CAST(round(m.valeur * (1 + e.evolution / 100)) AS integer) 
  FROM evolution AS e 
  WHERE m.code_dept = ANY(ARRAY['05','06','07','08']) 
    AND m.annee = e.annee;
  

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