SEO

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

> 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



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Excellent, I got it working...Thanks

Abdul-Wahid

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
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org