4 INSERT INTO config.upgrade_log (version) VALUES ('0137'); -- miker
6 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
8 bib biblio.record_entry%ROWTYPE;
9 output metabib.full_rec%ROWTYPE;
12 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
14 FOR field IN SELECT * FROM biblio.flatten_marc( bib.marc ) LOOP
16 output.ind1 := field.ind1;
17 output.ind2 := field.ind2;
18 output.tag := field.tag;
19 output.subfield := field.subfield;
20 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
21 output.value := naco_normalize(field.value, field.subfield);
23 output.value := field.value;
29 $func$ LANGUAGE PLPGSQL;
31 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
32 use Unicode::Normalize;
35 my $txt = lc(encode_utf8(shift));
39 $txt =~ s/\pM+//go; # Remove diacritics
41 $txt =~ s/\xE6/AE/go; # Convert ae digraph
42 $txt =~ s/\x{153}/OE/go;# Convert oe digraph
43 $txt =~ s/\xFE/TH/go; # Convert Icelandic thorn
45 $txt =~ tr/\x{2070}\x{2071}\x{2072}\x{2073}\x{2074}\x{2075}\x{2076}\x{2077}\x{2078}\x{2079}\x{207A}\x{207B}/0123456789+-/;# Convert superscript numbers
46 $txt =~ tr/\x{2080}\x{2081}\x{2082}\x{2083}\x{2084}\x{2085}\x{2086}\x{2087}\x{2088}\x{2089}\x{208A}\x{208B}/0123456889+-/;# Convert subscript numbers
48 $txt =~ tr/\x{0251}\x{03B1}\x{03B2}\x{0262}\x{03B3}/AABGG/; # Convert Latin and Greek
49 $txt =~ tr/\x{2113}\xF0\!\"\(\)\-\{\}\<\>\;\:\.\?\xA1\xBF\/\\\@\*\%\=\xB1\+\xAE\xA9\x{2117}\$\xA3\x{FFE1}\xB0\^\_\~\`/LD /; # Convert Misc
50 $txt =~ tr/\'\[\]\|//d; # Remove Misc
52 if ($sf && $sf =~ /^a/o) {
53 my $commapos = index($txt,',');
55 if ($commapos != length($txt) - 1) {
56 my @list = split /,/, $txt;
57 my $first = shift @list;
58 $txt = $first . ',' . join(' ', @list);
67 $txt =~ s/\s+/ /go; # Compress multiple spaces
68 $txt =~ s/^\s+//o; # Remove leading space
69 $txt =~ s/\s+$//o; # Remove trailing space
72 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;