467,136 Members | 1,330 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,136 developers. It's quick & easy.

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

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

Nov 22 '05 #1
  • viewed: 2267
Share:
2 Replies


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: te****@sigaev.ru

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #2

--- Teodor Sigaev <te****@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

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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Nigel J. Andrews | last post: by
1 post views Thread by psql-mail@freeuk.com | last post: by
reply views Thread by konf@chalu.cz | last post: by
2 posts views Thread by Fischer Ulrich | last post: by
1 post views Thread by 帅猛 | last post: by
3 posts views Thread by Marcel Boscher | last post: by
2 posts views Thread by Net Virtual Mailing Lists | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.