Full Text Search

Control of Full Text Search export

Several directives can be used to control how Ora2Pg exports the Oracle's Text search indexes. By default, CONTEXT indexes will be exported to PostgreSQL FTS indexes, while CTXCAT indexes will be exported as indexes using the pg_trgm extension.

• CONTEXT_AS_TRGM

  Forces Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using the pg_trgm extension. By default, CONTEXT indexes are translated into FTS indexes and CTXCAT indexes use pg_trgm. Most of the time using pg_trgm is sufficient, which is why this directive exists. You need to create the pg_trgm extension in the destination database before importing the objects:

CREATE EXTENSION pg_trgm;

• FTS_INDEX_ONLY

  By default, Ora2Pg creates a function-based index to translate Oracle Text indexes:

CREATE INDEX ON t_document
        USING gin(to_tsvector('pg_catalog.french', title));

You will have to rewrite the CONTAINS() clause using to_tsvector(), for example:

SELECT id,title FROM t_document
        WHERE to_tsvector(title) @@ to_tsquery('search_word');

To force Ora2Pg to create an extra tsvector column with dedicated triggers for FTS indexes, disable this directive. In this case, Ora2Pg will add the column as follows: ALTER TABLE t_document ADD COLUMN tsv_title tsvector; Then update the column to compute FTS vectors if data have been loaded before: UPDATE t_document SET tsv_title = to_tsvector('pg_catalog.french', coalesce(title,'')); To automatically update the column when a modification in the title column occurs, Ora2Pg adds the following trigger:

CREATE FUNCTION tsv_t_document_title() RETURNS trigger AS $$
BEGIN
       IF TG_OP = 'INSERT' OR new.title != old.title THEN
               new.tsv_title :=
               to_tsvector('pg_catalog.french', coalesce(new.title,''));
       END IF;
       return new;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_tsv_t_document_title BEFORE INSERT OR UPDATE
 ON t_document
 FOR EACH ROW EXECUTE PROCEDURE tsv_t_document_title();

When the Oracle text index is defined over multiple columns, Ora2Pg will use setweight() to set weights in the order of the column declarations.

• FTS_CONFIG

  Use this directive to force which text search configuration to use. When it is not set, Ora2Pg will autodetect the stemmer used by Oracle for each index and use pg_catalog.english if the information is not found.

• USE_UNACCENT

  If you want to perform text searches in an accent-insensitive way, enable this directive. Ora2Pg will create a helper function using unaccent() and create the pg_trgm indexes using this function. With FTS, Ora2Pg will redefine your text search configuration, for example:

CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr
        ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

then set the FTS_CONFIG ora2pg.conf directive to fr instead of pg_catalog.english.

When enabled, Ora2pg will create the wrapper function:

CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
    SELECT public.unaccent('public.unaccent', $1);
$$ LANGUAGE sql IMMUTABLE
   COST 1;

The indexes are exported as follows:

CREATE INDEX t_document_title_unaccent_trgm_idx ON t_document
    USING gin (unaccent_immutable(title) gin_trgm_ops);

In your queries, you will need to use the same function in the search to be able to use the function-based index. Example:

SELECT * FROM t_document
        WHERE unaccent_immutable(title) LIKE '%donnees%';

• USE_LOWER_UNACCENT

  Same as above but calls lower() in the unaccent_immutable() function:

CREATE OR REPLACE FUNCTION unaccent_immutable(text)
RETURNS text AS
$$
    SELECT lower(public.unaccent('public.unaccent', $1));
$$ LANGUAGE sql IMMUTABLE;