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

Сразу в бой:

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

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_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 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_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$$

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$$

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

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).

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

Similar Posts

2 Responses so far.

LEAVE A COMMENT Отменить ответ