Thread: tsearch2() with data from other table

Hi,

I have a table with items.
One field is site_user, which fk:s the user row that owns the item.

There are a few fields i throw to tsearch2() via an index right now,
but one user wanted his name to be in the search as well. So, what I
would want to do is:

CREATE FUNCTION euits(int)
RETURNS text AS 'select username || \' \' || firstname || \' \' ||
lastname from site_user where objectid = $1;' LANGUAGE SQL;

CREATE TRIGGER site_item_fts
BEFORE UPDATE OR INSERT ON site_item
FOR EACH ROW EXECUTE PROCEDURE
tsearch2(idxfti, name, keywords, keywords_cb, location_country,
location_city, media_source, description, euits, site_user);

Pg accepts this, but when I

update site_item
set
     description = description

I get:
Query executed in 78 ms, 6 Row(s) Affected
WARNING:  TSearch: 'site_user' is not of character type
WARNING:  TSearch: 'site_user' is not of character type
...

I suppose I am doing something stupid here? If I remove euits
I still receive the same error.

Any suggestions are very welcome.

Best regards,
Marcus

> CREATE FUNCTION euits(int)
> RETURNS text AS 'select username || \' \' || firstname || \' \' ||
> lastname from site_user where objectid = $1;' LANGUAGE SQL;
>
> CREATE TRIGGER site_item_fts
> BEFORE UPDATE OR INSERT ON site_item
> FOR EACH ROW EXECUTE PROCEDURE
> tsearch2(idxfti, name, keywords, keywords_cb, location_country,
> location_city, media_source, description, euits, site_user);

site_user is a table, isn't it?
tsearch2 trigger accepts only column's names and functions with prototype TEXT
func(TEXT).

For clarify, show your table's definitions.
--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Teodor Sigaev skrev:
>> CREATE FUNCTION euits(int)
>> RETURNS text AS 'select username || \' \' || firstname || \' \' ||
>> lastname from site_user where objectid = $1;' LANGUAGE SQL;
>>
>> CREATE TRIGGER site_item_fts
>> BEFORE UPDATE OR INSERT ON site_item
>> FOR EACH ROW EXECUTE PROCEDURE
>> tsearch2(idxfti, name, keywords, keywords_cb, location_country,
>> location_city, media_source, description, euits, site_user);
>
> site_user is a table, isn't it?
> tsearch2 trigger accepts only column's names and functions with
> prototype TEXT func(TEXT).
>
> For clarify, show your table's definitions.

Hi,

I'm sorry I was unclear,

create table site_user
(
  objectid integer
,firstname varchar[60]
,lastname varchar[60]
,username varchar[10]
...
) without oids;


create table site_item
(
  objectid integer
,site_user integer -- fk to site_user.objectid
,description text
,idxfti tsvector
...
) without oids;

So, when updating site_item I want to fetch the names from site_user and
  give this data to tsearch2() along with other stuff from site_item.

Best regards,
Marcus

>>> CREATE FUNCTION euits(int)
>>> RETURNS text AS 'select username || \' \' || firstname || \' \' ||
>>> lastname from site_user where objectid = $1;' LANGUAGE SQL;
>>>
>>> CREATE TRIGGER site_item_fts
>>> BEFORE UPDATE OR INSERT ON site_item
>>> FOR EACH ROW EXECUTE PROCEDURE
>>> tsearch2(idxfti, name, keywords, keywords_cb, location_country,
>>> location_city, media_source, description, euits, site_user);
<skip>
> So, when updating site_item I want to fetch the names from site_user and
>  give this data to tsearch2() along with other stuff from site_item.

Sorry, current interface of tsvector aggregate doesn't support it.

--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/