PostgreSQL执行数据回滚函数报uuid=text运算符不存在问题咨询

核心报错原因 #

你遇到的operator does not exist: uuid = text错误由以下几类问题共同导致，同时代码还存在其他逻辑、语法错误：

• 变量类型不匹配：你声明的test_val1、test_id均为text类型，但实际对应表字段均为uuid类型，做等值匹配时没有对应的运算符支持跨类型比较
• DELETE逻辑错误：匹配主表删除条件时，错误使用审计表的val1字段匹配主表的id字段，结合类型不匹配直接触发报错
• 语法错误：UPDATE语句中出现不存在的别名brb，且UPDATE的表重复定义导致逻辑异常
• 逻辑漏洞：针对INSERT类型的审计记录，删除逻辑不符合回退要求，你需要删除的是指定时间点之后新增/修改的记录，匹配字段应为审计记录的id和主表id

修正后的函数代码 #

create or replace function fun(test_val1_input uuid, test_val3_input text)
returns void
as $functions$
declare 
    test_id uuid ;
    test_val1 uuid ;  
    test_val2 text ; 
    test_val3 timestamp;
    test_val4 text ;
    cur cursor for 
        select id, val1 , val2 , val3, val4
        from test_table at 
        where val1 = test_val1_input 
          and val3 > to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS')
        order by val3 desc;
begin   
open cur;

fetch next from cur into test_id, test_val1 , test_val2 , test_val3 , test_val4;

while found
loop
    if (test_val4 = 'INSERT')
    then
        -- 匹配主表id和审计记录id，删除时间点后新增的记录
        delete from main_table mt where mt.id = test_id;
    elsif (test_val4 = 'UPDATE')
    then
        -- 先删除当前最新版本的记录
        delete from main_table mt where mt.id = test_id;
        -- 插入上一个版本的记录
        with cte as 
        (
            select * 
            from test_table at
            where at.val1 = test_val1 
              and at.val3 < test_val3 
              and at.val4 in ('INSERT','UPDATE')
            order by val3 desc
            limit 1
        )
        insert into main_table(id, val1, val2)
        select cte.id, cte.val1, cte.val2 from cte;
    end if;
    fetch next from cur into test_id, test_val1, test_val2, test_val3, test_val4;
end loop;
close cur;
end;
$functions$ language plpgsql;

调用方式 #

select fun('4d87-ad12-2f78c1c52b7a'::uuid, '2001-09-10 12:02:20');

调用后主表将仅保留指定时间点之前插入的2条记录，符合你预期的结果。

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