Thread: tsearch2 trigger alternative

Tsearch2 comes with its own tsearch2 trigger function. You pass column names to
it, and it puts a vanilla tsvector into the column named in TG_ARGV[0]. Not
only can you pass column names to it, but you can pass simple functions to it
as well. This is magical to me. :)

I'm trying to figure out how to do the same thing using plpgsql, except instead
of returning a vanilla tsvector, I want to return a specially weighted
tsvector. I've created a function that can do this:

create or replace function name_vector (text) returns tsvector as 'select
setweight(to_tsvector(substr($1,1,strpos($1,'',''))),''C'') ||
to_tsvector(substr($1,strpos($1,'','')+1,length($1)));' language 'sql';

so...

Plain:

select to_tsvector('Einstein, Albert');
       to_tsvector
-------------------------
 'albert':2 'einstein':1

Weighted:

select name_vector('Einstein, Albert');
       name_vector
--------------------------
 'albert':2 'einstein':1C


Now, to somehow package that into a magical trigger function...

All the examples for creating trigger functions that I've found use static
column names, NEW and OLD ... I would like to create a generic trigger
function, as the tsearch2 trigger function does, to return the specially
weighted tsvector.

Its like a lighter to a caveman. Can anyone lend a hand?

CG

__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools

Chris Gamache wrote:
> Tsearch2 comes with its own tsearch2 trigger function. You pass column names to
> it, and it puts a vanilla tsvector into the column named in TG_ARGV[0]. Not
> only can you pass column names to it, but you can pass simple functions to it
> as well. This is magical to me. :)
look at tsvector.c:864

numattr = SPI_fnumber(rel->rd_att, trigger->tgargs[i]);
if (numattr == SPI_ERROR_NOATTRIBUTE)
{
      funcoid = findFunc(trigger->tgargs[i]);
      if (funcoid == InvalidOid)
           ereport(ERROR,
                 (errcode(ERRCODE_UNDEFINED_COLUMN),
                  errmsg("could not find function or field \"%s\"",
                  trigger->tgargs[i])));
           continue;
}

If current args (trigger->tgargs[i]) isn't a column name, then it's a function
name. It's simple :)


>
> I'm trying to figure out how to do the same thing using plpgsql, except instead
> of returning a vanilla tsvector, I want to return a specially weighted
> tsvector. I've created a function that can do this:
I didn't work with plpgsql :(

--
Teodor Sigaev                                  E-mail: teodor@sigaev.ru

--- Teodor Sigaev <teodor@sigaev.ru> wrote:
>
>
> Chris Gamache wrote:
> > Tsearch2 comes with its own tsearch2 trigger function. You pass column
> names to
> > it, and it puts a vanilla tsvector into the column named in TG_ARGV[0]. Not
> > only can you pass column names to it, but you can pass simple functions to
> it
> > as well. This is magical to me. :)
> look at tsvector.c:864
>
> numattr = SPI_fnumber(rel->rd_att, trigger->tgargs[i]);
> if (numattr == SPI_ERROR_NOATTRIBUTE)
> {
>       funcoid = findFunc(trigger->tgargs[i]);
>       if (funcoid == InvalidOid)
>            ereport(ERROR,
>                  (errcode(ERRCODE_UNDEFINED_COLUMN),
>                   errmsg("could not find function or field \"%s\"",
>                   trigger->tgargs[i])));
>            continue;
> }
>
> If current args (trigger->tgargs[i]) isn't a column name, then it's a
> function
> name. It's simple :)

Its like a circuit board to Genghis Kahn... :) I still wouldn't be able to
quickly write my own trigger. It occurrs to me that this might be an
opportunity to properly extend tsearch2, though.

I'm sure that the tsearch2 trigger could detect a tsvector type and allow it to
be inserted without modification. That would solve this problem completely! I
can't think of any practical argument against it. It makes sense that others
besides myself would want to selectively weight columns upon insert without
having to write their own custom trigger in C.

CG

__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools