How do I use tsvector_update_trigger to index non-character columns?

From: dennis jenkins <dennis(dot)jenkins(dot)75(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How do I use tsvector_update_trigger to index non-character columns?
Date: 2009-09-03 13:09:22
Message-ID: 35a884aa0909030609i5294a5e3p22ef7a6f9b05a889@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.
I have several tables in a database that I want to use with "full
text searching". Some of the fields in some of these tables are not
character types, but for the purposes of searching, I'd like to
include their "character representation" in the tsvector.
Unfortunately, I cannot make this work without duplicating the column
(original as integer and dup as 'text' and using a trigger to
regenerate the dup on insert/update). I would prefer a "cleaner"
approach.

I've reduced the problem to a small amount of SQL (included below)
that illustrates my problem. Thank you kindly for any assistance.

capybara=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.1)
(1 row)

---------- Attempt #1:

capybara=# drop table if exists abc;
DROP TABLE

capybara=# create table abc (
abc_id serial not null,
client_num integer not null,
abc_name text not null,
tsv tsvector,
constraint abc_pkey primary key (abc_id)
) with (oids=false);
NOTICE: CREATE TABLE will create implicit sequence "abc_abc_id_seq"
for serial column "abc.abc_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"abc_pkey" for table "abc"
CREATE TABLE

capybara=# CREATE TRIGGER abc_tsv_update BEFORE INSERT OR UPDATE ON
abc FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('tsv',
'pg_catalog.english', 'client_num', 'abc_name');
CREATE TRIGGER

capybara=# insert into abc (client_num, abc_name) values (2751, 'bob');
ERROR: column "client_num" is not of character type

------- Attempt #2
-- same table, different trigger function:

capybara=# CREATE TRIGGER abc_tsv_update BEFORE INSERT OR UPDATE ON
abc FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('tsv',
'pg_catalog.english', 'cast (client_num as text)', 'abc_name');
CREATE TRIGGER

capybara=# insert into abc (client_num, abc_name) values (2751, 'bob');
ERROR: column "cast (client_num as text)" does not exist

Browse pgsql-general by date

  From Date Subject
Next Message Sim Zacks 2009-09-03 13:19:12 array weirdity
Previous Message Inigo Barandiaran 2009-09-03 12:47:54 Re: PosgreSQL Service does not Start In Vista