full-text-with-postgresql.sql

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