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