Open-ILS/src/sql/Pg/upgrade/0034.schema.in-db-indexing-normalization.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0034'); -- miker
   4
   5 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
   6 DECLARE
   7         normalizer      RECORD;
   8         value           TEXT := '';
   9 BEGIN
  10         value := NEW.value;
  11
  12         IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
  13                 FOR normalizer IN
  14                         SELECT  n.func AS func,
  15                                 n.param_count AS param_count,
  16                                 m.params AS params
  17                           FROM  config.index_normalizer n
  18                                 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
  19                           WHERE field = NEW.field
  20                           ORDER BY m.pos
  21                 LOOP
  22                         EXECUTE 'SELECT ' || normalizer.func || '(' ||
  23                                         quote_literal( value ) ||
  24                                         CASE
  25                                                 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
  26                                                 ELSE ''
  27                                         END ||
  28                                 ')' INTO value;
  29                 END LOOP;
  30         END IF;
  31
  32         IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
  33                 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
  34         ELSE
  35                 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
  36         END IF;
  37
  38         RETURN NEW;
  39 END;
  40 $$ LANGUAGE PLPGSQL;
  41
  42 COMMIT;
  43