2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
30 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
31 ELSE $2::TEXT::INT + 1
34 $$ LANGUAGE SQL STRICT IMMUTABLE;
36 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
37 use Unicode::Normalize;
43 $txt =~ s/\pM+//go; # Remove diacritics
45 $txt =~ s/\xE6/AE/go; # Convert ae digraph
46 $txt =~ s/\x{153}/OE/go;# Convert oe digraph
47 $txt =~ s/\xFE/TH/go; # Convert Icelandic thorn
49 $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
50 $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
52 $txt =~ tr/\x{0251}\x{03B1}\x{03B2}\x{0262}\x{03B3}/AABGG/; # Convert Latin and Greek
53 $txt =~ tr/\x{2113}\xF0\!\"\(\)\-\{\}\<\>\;\:\.\?\xA1\xBF\/\\\@\*\%\=\xB1\+\xAE\xA9\x{2117}\$\xA3\x{FFE1}\xB0\^\_\~\`/LD /; # Convert Misc
54 $txt =~ tr/\'\[\]\|//d; # Remove Misc
56 if ($sf && $sf =~ /^a/o) {
57 my $commapos = index($txt,',');
59 if ($commapos != length($txt) - 1) {
60 my @list = split /,/, $txt;
61 my $first = shift @list;
62 $txt = $first . ',' . join(' ', @list);
71 $txt =~ s/\s+/ /go; # Compress multiple spaces
72 $txt =~ s/^\s+//o; # Remove leading space
73 $txt =~ s/\s+$//o; # Remove trailing space
76 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
78 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT ) RETURNS TEXT AS $func$
79 SELECT public.naco_normalize($1,'');
80 $func$ LANGUAGE 'sql' STRICT IMMUTABLE;
82 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
83 SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
86 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
90 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
94 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
95 use Unicode::Normalize;
103 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
104 use Unicode::Normalize;
107 $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
112 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
115 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
117 if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
120 return (split /\s+/, $txt)[0];
122 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
124 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
125 SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
126 $$ LANGUAGE SQL STRICT IMMUTABLE;
128 CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
129 SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
130 $$ LANGUAGE SQL STABLE;
132 CREATE AGGREGATE public.first (
133 sfunc = public.first_agg,
134 basetype = anyelement,
138 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
140 $$ LANGUAGE SQL STABLE;
142 CREATE AGGREGATE public.last (
143 sfunc = public.last_agg,
144 basetype = anyelement,
148 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
156 $$ LANGUAGE SQL STABLE;
158 CREATE AGGREGATE public.agg_text (
159 sfunc = public.text_concat,
164 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
172 $$ LANGUAGE SQL STABLE;
174 CREATE AGGREGATE public.agg_tsvector (
175 sfunc = public.tsvector_concat,
180 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
184 $$ language 'plpgsql';
187 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
189 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
190 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
191 JOIN actor.org_unit a ON a.id = t.keyid
192 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
193 $$ LANGUAGE SQL STABLE;
195 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
197 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
198 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
199 JOIN actor.org_unit a ON a.id = t.keyid
200 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
201 $$ LANGUAGE SQL STABLE;
203 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
205 FROM connectby('actor.org_unit','id','parent_ou','name',
207 FROM actor.org_unit_ancestors($1) x
208 JOIN actor.org_unit_type y ON x.ou_type = y.id
211 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
212 JOIN actor.org_unit a ON a.id = t.keyid
213 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
214 $$ LANGUAGE SQL STABLE;
216 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
218 FROM actor.org_unit a
219 WHERE id = ( SELECT FIRST(x.id)
220 FROM actor.org_unit_ancestors($1) x
221 JOIN actor.org_unit_type y
222 ON x.ou_type = y.id AND y.depth = $2);
223 $$ LANGUAGE SQL STABLE;
225 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
227 FROM actor.org_unit_ancestors($1)
230 FROM actor.org_unit_descendants($1);
231 $$ LANGUAGE SQL STABLE;
233 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
234 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
235 $$ LANGUAGE SQL STABLE;
237 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
239 FROM actor.org_unit_ancestors($1)
242 FROM actor.org_unit_ancestors($2);
243 $$ LANGUAGE SQL STABLE;
245 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
247 FROM actor.org_unit_ancestors($1)
250 FROM actor.org_unit_ancestors($2);
251 $$ LANGUAGE SQL STABLE;
253 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
254 SELECT COUNT(id)::INT FROM (
255 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
257 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
259 $$ LANGUAGE SQL STABLE;
261 CREATE AGGREGATE array_accum (
262 sfunc = array_append,
263 basetype = anyelement,