Oracle函数转PostgreSQL遇阻，求可行转换方案

原Oracle代码的问题说明 #

原Oracle代码存在语法与逻辑缺陷，无法直接运行：

• 函数定义缺少返回类型（Oracle函数必须指定返回值）
• 游标声明位置错误，需放在DECLARE块中
• 循环未处理游标数据获取与退出条件，会导致无限循环
• 未关闭游标，存在资源泄漏风险

正确的PostgreSQL版本函数 #

CREATE OR REPLACE FUNCTION updateinsert()
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    -- 遍历符合条件的CCRs记录
    FOR rec IN
        SELECT id 
        FROM "CCRs"  
        WHERE status = 'POR REGULARIZAR' 
          AND id IN (221424, 188790)
    LOOP
        -- 更新CCRs表
        UPDATE "CCRs" 
        SET status = 'ANULADO', 
            isActive = false,
            cancelationDate = CURRENT_TIMESTAMP,
            updatedAt = CURRENT_TIMESTAMP
        WHERE id = rec.id;

        -- 插入历史记录
        INSERT INTO public."CCRHistories"
            (status, createdAt, updatedAt, ccrId, userId)
        VALUES ('ANULADO', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, rec.id, 2438);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

关键转换说明 #

• 函数结构：PostgreSQL函数需明确返回类型（此处无返回值，用RETURNS void），以$$作为代码块分隔符，指定执行语言为plpgsql
• 游标遍历：PostgreSQL推荐使用FOR ... IN SELECT的简化语法，无需显式声明、打开、关闭游标，自动完成遍历与资源释放
• 时间函数：now()在PostgreSQL中可用，但CURRENT_TIMESTAMP是SQL标准写法，兼容性更强
• 标识符大小写：PostgreSQL中带双引号的标识符区分大小写，需保持原表名的引号格式（如"CCRs"）与原代码一致
• 事务处理：函数默认在调用者的事务上下文执行，无需额外配置即可保证更新与插入操作的原子性

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