De duplicate contacts by foreign key

WebLog
2011-04-07
2013-05-28
  • An example for PostgreSQL of how to remove duplicated contacts by looking at their id_ref field:

    CREATE FUNCTION k_sp_dedup_contacts (CHAR) RETURNS INTEGER AS '
    DECLARE
      NuDDuped INTEGER;
      GuMaster CHAR(32);
      GuDupled CHAR(32);
      IdMaster VARCHAR(50);
      Dups RECORD;
      Contacts NO SCROLL CURSOR (wrka CHAR(32)) IS SELECT gu_contact,id_ref FROM k_contacts WHERE gu_workarea=wrka AND id_ref IS NOT NULL;
    BEGIN
      NuDDuped:=0;
      CREATE TEMPORARY TABLE k_temp_dup_contacts (gu_dup CHAR(32) CONSTRAINT pk_temp_dup_contacts PRIMARY KEY, gu_master CHAR(32)) ON COMMIT DROP;
      OPEN Contacts($1);
      FETCH Contacts INTO GuMaster,IdMaster;
      WHILE FOUND LOOP
        SELECT gu_dup INTO GuDupled FROM k_temp_dup_contacts WHERE gu_dup=GuMaster;
        IF NOT FOUND THEN
          INSERT INTO k_temp_dup_contacts SELECT gu_contact,GuMaster FROM k_contacts WHERE gu_workarea=$1 AND id_ref=IdMaster AND gu_contact<>GuMaster;
        END IF;
        FETCH Contacts INTO GuMaster,IdMaster;
      END LOOP;
      CLOSE Contacts;
      FOR Dups IN SELECT gu_dup,gu_master FROM k_temp_dup_contacts LOOP
        DELETE FROM k_oportunities WHERE gu_contact = Dups.gu_dup AND tl_oportunity IN (SELECT tl_oportunity FROM k_oportunities WHERE gu_contact = Dups.gu_master);
        UPDATE k_oportunities SET gu_contact = Dups.gu_master WHERE gu_contact = Dups.gu_dup;
        PERFORM k_sp_del_contact (Dups.gu_dup);
        NuDDuped:=NuDDuped+1;
      END LOOP;
      DROP TABLE k_temp_dup_contacts;
      return NuDDuped;
    END;
    ' LANGUAGE 'plpgsql';