Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Full Text Search en PostgreSQL, usando un índice GIN.
--
-- La ciencia está en:
--
-- * Agregar un campo en la tabla de tipo tsvector (1), en este caso se llama tsv,
-- podría ponérsele cualquier nombre.
-- * Agregar un índice GIN (2)
-- * Crear un trigger que cuando se inserte o actualice una tupla se actualice la
-- columna tsv (nuestro tsvector)
--
-- 1. http://www.postgresql.org/docs/9.1/static/datatype-textsearch.html
-- 2. http://www.postgresql.org/docs/9.1/static/textsearch-indexes.html
--
--
-- Tabla People
--
CREATE TABLE people
(
id serial NOT NULL,
nombre character varying(255) NOT NULL,
primer_apellido character varying(255) NOT NULL,
segundo_apellido character varying(255) NOT NULL,
tsv tsvector,
CONSTRAINT people_pkey PRIMARY KEY (id)
);
--
-- GIN Index
--
CREATE INDEX people_tsv_idx
ON people
USING gin
(tsv);
--
-- misc_people_generate_tsvector
--
CREATE OR REPLACE FUNCTION people_generate_tsvector()
RETURNS trigger AS
$BODY$
begin
new.tsv :=
setweight(to_tsvector('pg_catalog.spanish', unaccent( coalesce( new.nombre, ''))), 'A') ||
setweight(to_tsvector('pg_catalog.spanish', unaccent( coalesce( new.primer_apellido, ''))), 'B') ||
setweight(to_tsvector('pg_catalog.spanish', unaccent( coalesce( new.segundo_apellido, ''))), 'C');
return new;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--
-- Trigger
--
CREATE TRIGGER tsvector_misc_people_upsert_trigger
BEFORE INSERT OR UPDATE
ON people
FOR EACH ROW
EXECUTE PROCEDURE people_generate_tsvector();
--
-- mwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmwmw
--
--
-- Este query busca 15 registros en la tabla people y lo ordena de acuerdo al ranking
--
SELECT "people".*,
((ts_rank(("people"."tsv"),
(to_tsquery('spanish', ''' ' || unaccent('juan') || ' ''')), 0))) AS pg_search_rank
FROM "people"
WHERE ((("people"."tsv") @@ (to_tsquery('spanish', ''' ' || unaccent('juan') || ' '''))))
ORDER BY pg_search_rank DESC, "people"."id" ASC LIMIT 15
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.