Thread: Re: Tsearch2 trigger firing...

Re: Tsearch2 trigger firing...

From
"Net Virtual Mailing Lists"
Date:
Hello,

Thank you to Oleg for your help with this earlier!  It resolved it very
nicely!


I still have one remaining issue which I can't figure out, perhaps best
explained with an example:


CREATE TABLE sometable (
title    TEXT,
body     TEXT,
footer   TEXT,
all_fti  TSVECTOR
);

UPDATE sometable SET all_fti = setweight(to_tsvector(title), 'A') ||
setweight(to_tsvector(body),'B') || setweight(to_tsvector(footer), 'C');

INSERT INTO sometable (title, body, footer) VALUES ('something in the
title', 'something in the body', 'something in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('anything in the
title', 'anything in the body', 'anything in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('whatever in the
title', 'whatever in the body', 'whatever in the footer');


.. What I can't figure out is how to make those last 3 inserts
automatically update all_fti via a trigger... The documentation would
show something like:


CREATE TRIGGER sometable_update_fti BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(all_fti, title, body, footer);

.. but as expected, this does not take into account the result of the
"setweight" function....  Unfortunately, I am not very skilled with
Postgres's triggers (yet) and I can't find any documentation on how to go
about this...

Any thoughts?.....

- Greg


Re: Tsearch2 trigger firing...

From
Oleg Bartunov
Date:
On Sat, 16 Oct 2004, Net Virtual Mailing Lists wrote:

> Hello,
>
> Thank you to Oleg for your help with this earlier!  It resolved it very
> nicely!
>
>
> I still have one remaining issue which I can't figure out, perhaps best
> explained with an example:
>
>
> CREATE TABLE sometable (
> title    TEXT,
> body     TEXT,
> footer   TEXT,
> all_fti  TSVECTOR
> );
>
> UPDATE sometable SET all_fti = setweight(to_tsvector(title), 'A') ||
> setweight(to_tsvector(body),'B') || setweight(to_tsvector(footer), 'C');
>
> INSERT INTO sometable (title, body, footer) VALUES ('something in the
> title', 'something in the body', 'something in the footer');
> INSERT INTO sometable (title, body, footer) VALUES ('anything in the
> title', 'anything in the body', 'anything in the footer');
> INSERT INTO sometable (title, body, footer) VALUES ('whatever in the
> title', 'whatever in the body', 'whatever in the footer');
>
>
> .. What I can't figure out is how to make those last 3 inserts
> automatically update all_fti via a trigger... The documentation would
> show something like:
>
>
> CREATE TRIGGER sometable_update_fti BEFORE UPDATE OR INSERT ON sometable
> FOR EACH ROW EXECUTE PROCEDURE tsearch2(all_fti, title, body, footer);
>
> .. but as expected, this does not take into account the result of the
> "setweight" function....  Unfortunately, I am not very skilled with
> Postgres's triggers (yet) and I can't find any documentation on how to go
> about this...
>
> Any thoughts?.....
>

You're right, tsearch2 trigger  doesn't supports tsvector as argument yet.
The problem is in rather complex argument recognition. I don't know
any workaround, so take a look on sources or wait until we'll have spare
time to implement it.


> - Greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83