DROP TABLE PhonesPersons;
CREATE TABLE PhonesPersons (person INTEGER NOT NULL, -- Not UNIQUE since a person may have several phone numbers
                            phone TEXT NOT NULL -- Not UNIQUE since a phone can be used by several persons
);

CREATE INDEX phone_idx ON PhonesPersons(phone);

CREATE INDEX countrycode_idx ON PhonesPersons (substr(phone, 1, 3)); -- Works because substr is IMUTABLE


DROP TABLE EmailsPersons;
CREATE TABLE EmailsPersons (person INTEGER NOT NULL, -- Not UNIQUE since a person may have several email addresses
                            email TEXT NOT NULL -- Not UNIQUE since an email can be used by several persons
);

-- Extracts the TLD from a domain name (or an email address)
CREATE OR REPLACE FUNCTION tld(TEXT) RETURNS TEXT IMMUTABLE AS
   '
     DECLARE
       first_dot INTEGER;
       rest TEXT;
     BEGIN
       first_dot = strpos($1, ''.'');
       rest = substr($1, first_dot+1);
       IF strpos(rest, ''.'') = 0 THEN
          RETURN rest;
       ELSE
          RETURN last_label(rest);
       END IF;
     END;
   '
 LANGUAGE PLPGSQL;

CREATE INDEX email_idx ON EmailsPersons(email);

CREATE INDEX tld_idx ON EmailsPersons (tld(email));