Полнотекстовый поиск в PostgreSQL (8.4)

• 02, 12, 2010
•  bullgare
•  No category
• комментария 2

Сразу в бой:

Для нужных нам полей устанавливаем триггеры:
на изменение данных

CREATE TRIGGER "my_field_tr_after_insert_update_tsearch"
AFTER INSERT OR UPDATE ON my_field
FOR EACH ROW EXECUTE PROCEDURE my_field_trigger_on_insert_update();;

на удаление данных

CREATE TRIGGER "my_field_tr_after_delete_tsearch"
AFTER DELETE ON my_field
FOR EACH ROW EXECUTE PROCEDURE my_field_trigger_on_delete();

Соответствующие хранимые функции:
для обновления

create or replace function my_field_trigger_on_insert_update() returns trigger as $$
BEGIN
	PERFORM my_table_row_indexer( NEW );
	return NEW;
END;
$$
language $\$plpgsql$$

для удаления

create or replace function my_field_trigger_on_delete() returns trigger as $$
BEGIN
	DELETE FROM search_table
	WHERE search_table.[field_id] = OLD.[field_id];
	return OLD;
END;
$$
language $\$plpgsql$$

Соответственно, осталось только определить мега-функцию my_table_row_indexer

create or replace function my_table_row_indexer( [table_name] ) returns void as $$
DECLARE
	Param ALIAS FOR $1;
	existId integer DEFAULT NULL;
	Id integer DEFAULT NULL;
	indexData RECORD;

BEGIN
	SELECT [search_table].[search_id] INTO existId
		FROM [search_table]
		WHERE	[search_table].[field_id] = Param.[field_id];
	Id := Param.[field_id];
	
-- все простые поля для индексации с разбивкой по весам
SELECT 
COALESCE([table_name].[field1]::text, '') AS idx_dataA,
COALESCE([table_name].[field2]::text, '')||' '||
COALESCE([table_name].[field3]::text, '')||' '||
COALESCE([table_name].[field4]::text, '') AS idx_dataB,
COALESCE([table_name].[field5]::text, '')||' '||
COALESCE([table_name].[field6]::text, '') AS idx_dataC,
' ' AS idx_dataD, -- это если таких совсем нету
INTO indexData
FROM [table_name]
	WHERE [table_name].[field1] = Id;
	
-- заносим данные в таблицу индексов
	IF existId IS NULL THEN
		INSERT INTO [search_table] ([field_id], ts_vector)
			VALUES(Param.[field_id],
				setweight( to_tsvector( 'russian', indexData.idx_dataA::text ), 'A' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataA::text ), 'A' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataB::text ), 'B' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataB::text ), 'B' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataC::text ), 'C' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataC::text ), 'C' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataD::text ), 'D' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataD::text ), 'D' )
			);
	ELSE
		UPDATE [search_table] SET (ts_vector)
			= (
				setweight( to_tsvector( 'russian', indexData.idx_dataA::text || refIndexA ), 'A' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataA::text || refIndexA ), 'A' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataB::text || refIndexB ), 'B' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataB::text || refIndexB ), 'B' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataC::text || refIndexC ), 'C' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataC::text || refIndexC ), 'C' ) ||
				setweight( to_tsvector( 'russian', indexData.idx_dataD::text || refIndexD ), 'D' ) ||
				setweight( to_tsvector( 'english', indexData.idx_dataD::text || refIndexD ), 'D' )
			)
		WHERE [search_id] = existId;
	END IF;

	RETURN;
END;
$$
language $$plpgsql$$

При этом поиск производится так:

SELECT * 
FROM [table_name]
LEFT JOIN [search_table]
	ON [search_table].[field_id] = [table_name].[field_id]
	WHERE [search_table].ts_vector @@ (plainto_tsquery('russian', 'безопасный текст') || plainto_tsquery('english', 'безопасный текст'))
ORDER BY
	ts_rank_cd(ts_vector,(plainto_tsquery('russian', 'безопасный текст') || plainto_tsquery('english', 'безопасный текст')), 32) DESC
LIMIT 20, 30

Можно заметить интересное число «32» — по этому поводу можно почитать мануал по PostgreSQL (глава 12.3.3. Ranking Search Results).