There are three ways to get TSearch2 implemented in your PostgreSQL installation.
- Download and compile the source
- Download and install the contrib rpm (if you are running Linux)
- Purchase a binary distribution
Compiling PostgreSQL is outside the scope of this article. However, I will cover compiling and installing the TSearch2 module itself.
The TSearch2 module is included with the PostgreSQL source and can be found within the root of the tree at contrib/tsearch2.
After you have configured and installed PostgreSQL itself, you need to perform two more simple steps to get Tsearch2 installed. From the root of the PostgreSQL source tree, enter the following two commands:
make; make install
That's it. The module is now installed. Of course you still have to configure PostgreSQL for its use. For the examples in this article, you'll use a development database called Fingerless
(long story) to test TSearch2.
To configure the database to use TSearch2 you need to execute the tsearch2.sql file against the fingerless database. To do that, enter the following psql command:
psql fingerless < tsearch2.sql
Executing the script produces some noisy output as it creates sample tables and indexes for testing Tsearch2. Don't worry about all this output. When the script completes it will have added the following tables to your database (see Table 1):
Table 1. A list of tables added by executing the tsearch2.sql file.
You will also have a detailed list of new functions and data types. For more information on what the script actually adds to your database please see the tsearch2.sql
file or the Web site documentation
The billing application keeps the main billing details in the table named public.cmd_contact_task_entries
The table has the structure shown in Table 2:
Table 2: Structure of the public.cmd_contact_task_entries table.
|entry_id||integer||not null default nextval('"cmd_contact_task_e_entry_id_seq"'::text)
|creator_employee_id||integer || |
|description||text || |
| time_spent||interval || |
| created || timestamp with time zone || |
| billed ||timestamp with time zone || |
| hourly_rate || numeric(6,2) || |
| internal_notes || text ||default ''::text
| contact_id ||integer || |
| note_id ||integer ||default 0|
The table also has this index:
primary key, btree (entry_id)