O cómo buscar rápidamente en campos de texto en PostgreSQL a partir de la versión 8.2. Para ello implementa un tipo especial de índice que puede ser usado para la indexación de texto completo. Antes de entrar en detalles, mencionar que para que funcione el script debes tener instalado PostgreSQL con el módulo Tsearch2 y el diccionario en el correspondiente idioma, en nuestro caso 'spanish'. Es importante tener bien instalado y configurado el diccionario para que genere correctamente los lexemas.

El siguiente ejemplo muestra un ejemplo de búsqueda en una tabla que almacena titulos de películas y la sinopsis. En lo que nos ayuda Tsearch2, además de la velocidad de respuesta de la consulta, es capaz de otorgar pesos a las palabras que buscamos. veremos que se trata de una poderosa herramienta para búsqueda de texto


Estructura de la tabla
CREATE TABLE film
(
id_film int4,
titulo varchar(100),
sinopsis text
);


Ahora, a llenarla, por ejemplo, con un millón de registros. ;)

INSERT INTO film
VALUES ('1', 'Grease', 'En 1958, durante sus vacaciones de verano, Danny Zuko (John Travolta) y Sandy Olsson (Olivia Newton-John) se reúnen en una playa. Escenas de sus vacaciones se muestran, pero el verano termina, y Sandy y Danny decir su último adiós...');
INSERT INTO film
VALUES ('2', 'No es lugar para viejos', '...');
INSERT INTO film
VALUES ('3', 'Dos hombres y un destino', '...');

[...]

El siguiente paso es crear el campo especial del tipo tsvector con el siguiente comando.

ALTER TABLE film ADD COLUMN idx tsvector;


Marcamos el peso de cada campo con las letras A, B, C, D. Esto nos sirve para indicar en que campo o columna de nuestra tabla tienen más relevancia los térrminos posteriores de búsqueda. En nuestro ejemplo estamos indicando que el texto búscado es más relevante si aparece en el título que en la sinopsis.

La función to_tsvector genera el contenido del campo idx similar a los siguiente:
"Dos hombres y un destino";"'dos':1 'hombr':2 'destin':3"

UPDATE film SET idx = ( setweight ( to_tsvector (titulo), 'A') ||
setweight ( to_tsvector (sinopsis), 'B' ));

Generamos el índice con la función gin (Indice Generalizado Invertido), que según la documentación de PostgreSQL proporciona una forma más escalable y programable de indexar datos semi-estructurados y texto.

CREATE INDEX idx_films ON film USING gin(idx);

Pues ya sólo nos queda probar una consulta. Hemos utilizado ts_headline que sirve para marcar en negrita las palabras encontradas en la cadena de texto devuelta.

Para establecer el criterio de ordenación utilizamos ts_rank_cd que asigna un valor al peso asociado a cada campo anteriormente con setweight. Los valores pueden oscilar entre 0 y 1 y en éste caso hemos otorgado un 90% al título (marcado como A) y un 10% a la descripción (marcado como B).

SELECT titulo, descripcion, therank,
ts_headline(titulo || ' ' || descripcion, q) as summary
FROM (SELECT titulo, descripcion,
ts_rank_cd('(0.9,0.10)', idx, q) as therank, q
FROM film,
to_tsquery('(chocolate | secretaria | coche) & (arnold )') as q
WHERE idx @@ q
ORDER BY therank DESC
LIMIT 3) As results;