Hi,
I have created a simple plpgsql function as follows as included below.
The function works fine but I need to have this done automatically on
updates and inserts. So I have a table
CREATE TABLE addresses (
address_id serial PRIMARY KEY,
company varchar(250),
fname varchar(100),
lname varcahr(100,
...etc...
hash_company varchar(250),
hash_fname varchar(100),
hash_lname varchar(100)
);
The idea is, that the hashify_text function below is called for each
(required) field e.g. comapany, fname, lname etc. and a hash version
(using the word hash in a loose sense here) is created. The hash
version is then used for quick db selects so that searchin for...
'A B C Ltd.' would find the strings 'ABC ltd', 'A.B.C. ltd.', 'A B C LTD' etc.
So how can I create a trigger to automatically update the hash fields
on updates and inserts?
CREATE FUNCTION hashify_text(TEXT) RETURNS TEXT AS '
DECLARE
out_text TEXT := '''';
in_text TEXT;
index INTEGER := 0;
max INTEGER;
tmp CHAR;
BEGIN
in_text := $1;
max = char_length(in_text);
FOR i IN 1 .. max LOOP
tmp = upper(substring(in_text from i for 1));
IF ( strpos(''01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'', tmp) > 0 ) THEN
out_text := out_text || tmp;
END IF;
END LOOP;
RETURN out_text;
END;
' LANGUAGE 'plpgsql';
Regards,
Abdul-Wahid
