PostgreSQL 9.5异常处理实现：对标Oracle存储过程示例需求

需求背景 #

已开发Oracle存储过程用于处理脚本执行时的各类异常，现需在PostgreSQL 9.5中实现相同逻辑。由于PostgreSQL 9.5不支持IF NOT EXISTS语法，需捕获并处理「列已存在」类异常。

Oracle原存储过程代码 #

CREATE OR REPLACE PROCEDURE exec_neo_command (str IN VARCHAR2)
IS
  col_already_exists EXCEPTION;
  object_already_exists EXCEPTION;
  columns_indexed EXCEPTION;
  col_alreadyNull EXCEPTION;
  col_alreadyNull2 EXCEPTION;
  
  PRAGMA EXCEPTION_INIT(object_already_exists, -955);
  PRAGMA EXCEPTION_INIT(col_already_exists, -01430);
  PRAGMA EXCEPTION_INIT(columns_indexed, -1408);
  PRAGMA EXCEPTION_INIT(col_alreadyNull, -01442);
  PRAGMA EXCEPTION_INIT(col_alreadyNull2, -1451);
BEGIN
-- dbms_output.put_line (str || ' = command in exec_neo_command'); -- FOR DEBUGGING ONLY
  EXECUTE IMMEDIATE str;
EXCEPTION
  WHEN col_already_exists OR object_already_exists OR columns_indexed THEN
   dbms_output.put_line(str || ': Already exists, skipping...'); 
   
  WHEN col_alreadyNull OR col_alreadyNull2 THEN
  dbms_output.put_line(str || ': Already NULL, skipping...'); 
END exec_neo_command;

Oracle执行示例 #

BEGIN
       exec_neo_command ('ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0');
       exec_neo_command ('ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0');
       exec_neo_command ('ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0');             
END;
/   

需处理的PostgreSQL错误 #

ERROR:  column "idisabled" of relation "xtkworkflow" already exists
ERROR:  column "imaxpersotime" of relation "nmsdelivery" already exists

PostgreSQL 9.5 等价PL/pgSQL实现 #

PostgreSQL通过EXCEPTION块结合SQLSTATE错误码实现异常捕获。「列已存在」对应的SQLSTATE是42701（重复定义类错误），以下是等价实现：

CREATE OR REPLACE PROCEDURE exec_neo_command(str TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 执行传入的SQL命令
    EXECUTE str;
EXCEPTION
    -- 匹配列已存在、对象已存在等重复定义类错误
    WHEN SQLSTATE '42701' THEN
        RAISE NOTICE '%: 已存在，跳过执行...', str;
    -- 匹配与列NULL属性相关的错误（对应Oracle的-01442、-1451）
    -- 可根据实际抛出的SQLSTATE调整，示例为常见的NULL属性冲突错误码
    WHEN SQLSTATE '42804' THEN
        RAISE NOTICE '%: 已为NULL属性，跳过执行...', str;
END;
$$;

执行示例 #

CALL exec_neo_command('ALTER TABLE XtkEnumValue ADD iOrder NUMERIC(20) DEFAULT 0');
CALL exec_neo_command('ALTER TABLE XtkReport ADD iDisabled NUMERIC(3) DEFAULT 0');
CALL exec_neo_command('ALTER TABLE XtkWorkflow ADD iDisabled NUMERIC(3) DEFAULT 0');

关键说明 #

1. 用RAISE NOTICE替代Oracle的dbms_output.put_line，执行后会在控制台输出提示信息。
2. SQLSTATE 42701涵盖了列已存在、对象已存在等重复定义场景，对应Oracle中的-955、-01430、-1408错误码。
3. 针对列NULL属性的错误，需根据PostgreSQL实际抛出的SQLSTATE调整，示例中的42804对应「无法将列设置为NOT NULL，因为已有NULL值」的场景。
4. PostgreSQL 9.5使用CALL语句调用存储过程，替代Oracle的BEGIN...END块调用方式。

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