I have a trigger which needs to make a match on a phone number, so far this works, but it seems rather not optimised:
IF NEW.caller LIKE '44%' THEN
caller_tmp := SUBSTRING(NEW.caller FROM 3);
IF caller_tmp ~ '^[0-9]+$' THEN
NEW.caller_name := (SELECT phonenumber_name FROM phonebook WHERE caller_tmp::BIGINT LIMIT 1);
END IF;
ELSEIF NEW.caller LIKE '+44%' THEN
caller_tmp := SUBSTRING(NEW.caller FROM 4);
IF caller_tmp ~ '^[0-9]+$' THEN
NEW.caller_name := (SELECT phonenumber_name FROM phonebook WHERE caller_tmp::BIGINT LIMIT 1);
END IF;
ELSEIF NEW.caller LIKE '0044%' THEN
caller_tmp := SUBSTRING(NEW.caller FROM 5);
IF caller_tmp ~ '^[0-9]+$' THEN
NEW.caller_name := (SELECT phonenumber_name FROM phonebook WHERE caller_tmp::BIGINT LIMIT 1);
END IF;
END IF;
I need to check if the phone number is from the UK with either containing 44, +44 or 0044 in the front. If it does, I need to check if it's a valid integer, and then do my matching.
Could this be optimised for better perfomance?