Обсуждение: 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

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