001-rhnpackageevr-rhn_pe_v_r_e_uq.sql.postgresql

-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8

create or replace function fix_rhn_pe_v_r_e_uq()
returns void as
$$
declare original record;
declare duplicate record;
declare nevra_orig record;
declare nevra_dup record;
begin
  for original in select min(id) as id
          from rhnpackageevr
      group by version, release, epoch
        having count(*) > 1 loop
      for duplicate in select evr2.id
             from rhnpackageevr evr1, rhnpackageevr evr2
            where evr1.version = evr2.version
              and evr1.release = evr2.release
              and (evr1.epoch = evr2.epoch
               or (evr1.epoch is NULL and evr2.epoch is NULL))
              and evr1.id = original.id loop
      if (select count(*) from rhnpackagenevra where evr_id = duplicate.id) > 0 then
        original := duplicate;
        exit;
      end if;
    end loop;
    for duplicate in select evr2.id
             from rhnpackageevr evr1, rhnpackageevr evr2
            where evr1.version = evr2.version
              and evr1.release = evr2.release
              and (evr1.epoch = evr2.epoch
               or (evr1.epoch is NULL and evr2.epoch is NULL))
              and evr1.id != evr2.id
              and evr1.id = original.id loop
      update rhnactionpackage set evr_id = original.id where evr_id = duplicate.id;
      update rhnactionpackageremovalfailure set evr_id = original.id where evr_id = duplicate.id;
      update rhnchannelnewestpackage set evr_id = original.id where evr_id = duplicate.id;
      update rhnpackage set evr_id = original.id where evr_id = duplicate.id;
      select id into nevra_orig from rhnpackagenevra where evr_id = original.id;
      for nevra_dup in select id
                from rhnpackagenevra
               where evr_id = duplicate.id loop
        delete from rhnsnapshotpackage where nevra_id = nevra_dup.id and snapshot_id in (select snapshot_id from rhnsnapshotpackage where nevra_id = nevra_orig.id intersect select snapshot_id from rhnsnapshotpackage where nevra_id = nevra_dup.id);
        update rhnsnapshotpackage set nevra_id = nevra_orig.id where nevra_id = nevra_dup.id;
      end loop;
      delete from rhnpackagenevra where evr_id = duplicate.id;
      update rhnproxyinfo set proxy_evr_id = original.id where proxy_evr_id = duplicate.id;
      update rhnserveractionverifymissing set package_evr_id = original.id where package_evr_id = duplicate.id;
      update rhnserveractionverifyresult set package_evr_id = original.id where package_evr_id = duplicate.id;
      update rhnsatelliteinfo set evr_id = original.id where evr_id = duplicate.id;
      update rhnservercrash set package_evr_id = original.id where package_evr_id = duplicate.id;
      update rhnserverprofilepackage set evr_id = original.id where evr_id = duplicate.id;
      update rhntransactionpackage set evr_id = original.id where evr_id = duplicate.id;
      update rhnversioninfo set evr_id = original.id where evr_id = duplicate.id;
      update rhnserverpackage set evr_id = original.id where evr_id = duplicate.id;
      delete from rhnpackageevr where id = duplicate.id;
    end loop;
  end loop;
end;
$$ language plpgsql;

drop trigger if exists rhn_pack_evr_no_updel_trig on rhnpackageevr;

select fix_rhn_pe_v_r_e_uq();

drop function fix_rhn_pe_v_r_e_uq();

create trigger
rhn_pack_evr_no_updel_trig
before update or delete on rhnpackageevr
execute procedure no_operation_trig_fun();

drop index if exists rhn_pe_v_r_e_uq;
drop index if exists rhn_pe_v_r_uq;

create unique index rhn_pe_v_r_e_uq
    on rhnpackageevr (version, release, epoch)
 where epoch is not null;

create unique index rhn_pe_v_r_uq
    on rhnpackageevr (version, release)
 where epoch is null;