postgresql - 插入后不执行删除 - postgres

Question

我正在编写一个函数，我可以在其中存储表中的特定字段，然后再删除记录，以保留历史记录。

插入运行良好，但Delete有时会返回Delete 0，即使我对id要删除的字段进行了硬编码。

这是我的功能：

create or replace function deletefromtable(_schema text, _table text, _filter text, _userid int)
returns json as
$func$
DECLARE _record json;
DECLARE target text;
DECLARE mykey TEXT;
DECLARE newvalue TEXT;
DECLARE oldvalue TEXT;
DECLARE columnname TEXT;

begin
    
    SET session_replication_role = replica;

    execute format ('select row_to_json(t) from (select * from ' || _schema ||'.' || _table || ' WHERE ' || _filter || ' ) t') into _record;

    raise notice 'record: %', _record;
    
    FOR target IN SELECT col from track_settings(_table) LOOP
    
        with vw_listing (new_record) as ( values 
            (_record::jsonb)
        )
        SELECT (new_record ->> target)::text INTO newvalue
        FROM vw_listing LIMIT 1;
        
        raise notice 'newvalue: %', newvalue;
        execute format ('insert into track_history (created_at, table_name, column_name, table_id, user_id, new_val, old_val, pg_user) 
                    values (''' || Now() || ''', ''' || _table || ''', ''' || target || ''', ''' || _filter || ''' ,' || _userid || ', null, ''' || newvalue || ''', current_user  )');
    
    END LOOP;
    
    execute ('DELETE FROM public.user WHERE id = 1 ;');  -- THIS LINE IS NOT EXECUTED
     
    SET session_replication_role = default;

    RETURN _record;
    
    COMMIT;

end
$func$ language plpgsql;

我试图启用SET session_replication_role = replica;或SET session_replication_role = default;但仍然无法正常工作。

该函数没有任何错误并且正在执行所有语句。有人可以帮我解决吗？

标签： postgresql