need trigger help
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
So how can I create a trigger to automatically update the hash fields
on updates and inserts?
Something like the following works for me:
create or replace function public.my_trigger()
returns trigger as '
NEW.hashfield = hashfunction(NEW.data1,NEW.data2);
RETURN NEW;
END;
' language 'plpgsql';
--------
create trigger my_trig
before insert or update on my_tablename
for each row
execute procedure public.my_trigger();
The 'RETURN NEW' part is very important, without it your hash field won't
get updated at all.
--
Mike Nolan
Excellent, I got it working...Thanks ;)
Abdul-Wahid
Show quoted text
On 5/9/05, Mike Nolan <nolan@gw.tssi.com> wrote:
So how can I create a trigger to automatically update the hash fields
on updates and inserts?Something like the following works for me:
create or replace function public.my_trigger()
returns trigger as 'NEW.hashfield = hashfunction(NEW.data1,NEW.data2);
RETURN NEW;
END;
' language 'plpgsql';--------
create trigger my_trig
before insert or update on my_tablename
for each row
execute procedure public.my_trigger();The 'RETURN NEW' part is very important, without it your hash field won't
get updated at all.
--
Mike Nolan