Thread: How to create tsvector_update_trigger on Non-character type data
Hi,
How can we create tsvector update trigger on Non-character data type.
For example, i have created a ts vector trigger something like this.
CREATE TRIGGER tr_doc_id_col
BEFORE INSERT OR UPDATE
ON document
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('tsv_doc_id', 'pg_catalog.english', doc_id');
BEFORE INSERT OR UPDATE
ON document
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('tsv_doc_id', 'pg_catalog.english', doc_id');
Here,
tr_doc_id_col -- Name of the trigger
document -- Name of the table
tsv_doc_id -- tsvector form of the doc_id
doc_id -- Name of the column. It's data type is integer
This trigger should update the tsv_doc_id, when there is insert, delete or update happens on doc_id column.
But, the trigger is throwing an error saying that doc_id is not of character type (i.e it is not able to update based on non-character type column).
I have tried creating same kind of triggers on columns like title, body which are text data type. In this case it is working very well, but in the earlier case.
Can any of you tell me how to do in the case of non-character data type like doc_id?
Thanks,
Gaini Rajeshwar
On Oct 13, 2009, at 11:21 PM, Gaini Rajeshwar wrote:
> doc_id -- Name of the column. It's data type is integer
The strict error message is correct: The full-text search feature of
PostgreSQL can only index text strings, and doc_id (as an integer) is
not a text string. What precisely are you attempting to do? Do you
want to index the text version of the doc_id field (for example, if
doc_id is 12345, you want to include the literal string "12345" in the
index), or is doc_id a key into another table, and you want to include
some text fields from that other table in the index?
--
-- Christophe Pettus
xof@thebuild.com