如何将Oracle的'if sqlcode'逻辑迁移至PostgreSQL？解决SQLSTATE报错

问题描述 #

将Oracle中通过sqlcode判断SQL执行结果的逻辑迁移到PostgreSQL时，直接使用sqlstate进行判断出现如下报错：

SQL Error [42703]: ERROR: 列"sqlstate"不存在

原Oracle代码 #

参数：in iv_column_1, in iv_column_2, inout iv_msg

begin 
  update main_table set column_1 = iv_column_1 where column_2 = iv_column_2;
  if sqlcode <> 0 then
    iv_msg := 'main_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
  insert into history_table (column_1 , column_2) values (iv_column_1, iv_column_2);
  if sqlcode <> 0 then
    iv_msg := 'history_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
end;

错误的PostgreSQL代码 #

参数：in iv_column_1, in iv_column_2, inout iv_msg

begin 
  update main_table set column_1 = iv_column_1 where column_2 = iv_column_2;
  if sqlstate <> 0 then
    iv_msg := 'main_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
  insert into history_table (column_1 , column_2) values (iv_column_1, iv_column_2);
  if sqlstate <> 0 then
    iv_msg := 'history_table error '||CHR (13)||CHR (10)||SQLERRM;
    return;
  end if;
end;

报错原因 #

1. PostgreSQL中sqlstate是异常处理上下文专属变量，仅在EXCEPTION块内有效，正常执行逻辑中直接引用会被解析为表列名，导致“列不存在”的错误。
2. 即使能引用sqlstate，它的类型是5位字符串（比如成功状态为'00000'），而非Oracle中sqlcode那样的数字，用<> 0进行判断逻辑本身也不成立。

正确的PostgreSQL实现 #

方案1：局部异常捕获（贴合原Oracle逻辑） #

通过局部BEGIN...EXCEPTION块包裹每个SQL操作，捕获执行错误并设置返回信息，完全匹配原Oracle中“执行后检查错误、报错则返回”的逻辑：

begin 
  -- 处理main_table更新操作
  begin
    update main_table set column_1 = iv_column_1 where column_2 = iv_column_2;
  exception
    when others then
      iv_msg := 'main_table error '||CHR(13)||CHR(10)||SQLERRM;
      return;
  end;

  -- 处理history_table插入操作
  begin
    insert into history_table (column_1 , column_2) values (iv_column_1, iv_column_2);
  exception
    when others then
      iv_msg := 'history_table error '||CHR(13)||CHR(10)||SQLERRM;
      return;
  end;
end;

方案2：通过GET DIAGNOSTICS获取状态 #

如果需要在无异常的场景下获取执行状态（比如检查更新行数为0的情况），可以使用GET DIAGNOSTICS显式获取sqlstate：

begin 
  update main_table set column_1 = iv_column_1 where column_2 = iv_column_2;
  
  declare
    v_sqlstate text;
  begin
    GET DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE;
    if v_sqlstate <> '00000' then
      iv_msg := 'main_table error '||CHR(13)||CHR(10)||SQLERRM;
      return;
    end if;
  end;

  insert into history_table (column_1 , column_2) values (iv_column_1, iv_column_2);
  
  declare
    v_sqlstate text;
  begin
    GET DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE;
    if v_sqlstate <> '00000' then
      iv_msg := 'history_table error '||CHR(13)||CHR(10)||SQLERRM;
      return;
    end if;
  end;
end;

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