need trigger help

[Abdul-Wahid Paterson <abdulwahid@xxxxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq