1 -- ARG! need to rid ourselves of the broken table definition ... this mechanism is not ideal, sorry.
2 DROP TABLE config.index_normalizer CASCADE;
6 INSERT INTO config.upgrade_log (version) VALUES ('0032'); -- miker
8 -- Some handy functions, based on existing ones, to provide optional ingest normalization
10 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
11 SELECT SUBSTRING($1,$2);
12 $func$ LANGUAGE SQL STRICT IMMUTABLE;
14 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
15 SELECT SUBSTRING($1,1,$2);
16 $func$ LANGUAGE SQL STRICT IMMUTABLE;
18 CREATE OR REPLACE FUNCTION public.naco_normalize_keep_comma( TEXT ) RETURNS TEXT AS $func$
19 SELECT public.naco_normalize($1,'a');
20 $func$ LANGUAGE SQL STRICT IMMUTABLE;
22 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
23 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
24 $func$ LANGUAGE SQL STRICT IMMUTABLE;
26 -- And ... a table in which to register them
28 CREATE TABLE config.index_normalizer (
29 id SERIAL PRIMARY KEY,
30 name TEXT UNIQUE NOT NULL,
31 description TEXT UNIQUE NOT NULL,
33 param_count INT NOT NULL DEFAULT 0
36 CREATE TABLE config.metabib_field_index_norm_map (
37 id SERIAL PRIMARY KEY,
38 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
39 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
41 pos INT NOT NULL DEFAULT 0