8974c7e36bf4183913926730560f7e9ef9ba0fd4
[Evergreen.git] / Open-ILS / src / sql / Pg / 020.schema.functions.sql
1 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
2         SELECT  SUBSTRING(
3                         REGEXP_REPLACE(
4                                 REGEXP_REPLACE(
5                                         $1,
6                                         E'\W*$',
7                                         ''
8                                 ),
9                                 '  ',
10                                 ' '
11                         ),
12                         CASE
13                                 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
14                                 ELSE $2::TEXT::INT + 1
15                         END
16                 );
17 $$ LANGUAGE SQL STRICT IMMUTABLE;
18
19 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
20     use Unicode::Normalize;
21
22         my $txt = lc(shift);
23         my $sf = shift;
24
25     $txt = NFD($txt);
26         $txt =~ s/\pM+//go;     # Remove diacritics
27
28         $txt =~ s/\xE6/AE/go;   # Convert ae digraph
29         $txt =~ s/\x{153}/OE/go;# Convert oe digraph
30         $txt =~ s/\xFE/TH/go;   # Convert Icelandic thorn
31
32         $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
33         $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
34
35         $txt =~ tr/\x{0251}\x{03B1}\x{03B2}\x{0262}\x{03B3}/AABGG/;             # Convert Latin and Greek
36         $txt =~ tr/\x{2113}\xF0\!\"\(\)\-\{\}\<\>\;\:\.\?\xA1\xBF\/\\\@\*\%\=\xB1\+\xAE\xA9\x{2117}\$\xA3\x{FFE1}\xB0\^\_\~\`/LD /;     # Convert Misc
37         $txt =~ tr/\'\[\]\|//d;                                                 # Remove Misc
38
39         if ($sf && $sf =~ /^a/o) {
40                 my $commapos = index($txt,',');
41                 if ($commapos > -1) {
42                         if ($commapos != length($txt) - 1) {
43                                 my @list = split /,/, $txt;
44                                 my $first = shift @list;
45                                 $txt = $first . ',' . join(' ', @list);
46                         } else {
47                                 $txt =~ s/,/ /go;
48                         }
49                 }
50         } else {
51                 $txt =~ s/,/ /go;
52         }
53
54         $txt =~ s/\s+/ /go;     # Compress multiple spaces
55         $txt =~ s/^\s+//o;      # Remove leading space
56         $txt =~ s/\s+$//o;      # Remove trailing space
57
58         return $txt;
59 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
60
61 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT ) RETURNS TEXT AS $func$
62         SELECT public.naco_normalize($1,'');
63 $func$ LANGUAGE 'sql' STRICT IMMUTABLE;
64
65 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
66     SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
67 $$ LANGUAGE SQL;
68
69 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
70     return lc(shift);
71 $$ LANGUAGE PLPERLU;
72
73 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
74     return uc(shift);
75 $$ LANGUAGE PLPERLU;
76
77 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
78     use Unicode::Normalize;
79
80     my $x = NFD(shift);
81     $x =~ s/\pM+//go;
82     return $x;
83
84 $$ LANGUAGE PLPERLU;
85
86 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
87     use Unicode::Normalize;
88
89     my $x = NFC(shift);
90     $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
91     return $x;
92
93 $$ LANGUAGE PLPERLU;
94
95 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
96         my $txt = shift;
97         $txt =~ s/^\s+//o;
98         $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
99         $txt =~ s/\s+$//o;
100         if (/(\d{3}(?:\.\d+)?)/o) {
101                 return $1;
102         } else {
103                 return (split /\s+/, $txt)[0];
104         }
105 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
106
107 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
108         SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
109 $$ LANGUAGE SQL STRICT IMMUTABLE;
110
111 CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
112         SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
113 $$ LANGUAGE SQL STABLE;
114
115 CREATE AGGREGATE public.first (
116         sfunc    = public.first_agg,
117         basetype = anyelement,
118         stype    = anyelement
119 );
120
121 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
122         SELECT $2;
123 $$ LANGUAGE SQL STABLE;
124
125 CREATE AGGREGATE public.last (
126         sfunc    = public.last_agg,
127         basetype = anyelement,
128         stype    = anyelement
129 );
130
131 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
132 SELECT
133         CASE    WHEN $1 IS NULL
134                         THEN $2
135                 WHEN $2 IS NULL
136                         THEN $1
137                 ELSE $1 || ' ' || $2
138         END;
139 $$ LANGUAGE SQL STABLE;
140
141 CREATE AGGREGATE public.agg_text (
142         sfunc    = public.text_concat,
143         basetype = text,
144         stype    = text
145 );
146
147 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
148 SELECT
149         CASE    WHEN $1 IS NULL
150                         THEN $2
151                 WHEN $2 IS NULL
152                         THEN $1
153                 ELSE $1 || ' ' || $2
154         END;
155 $$ LANGUAGE SQL STABLE;
156
157 CREATE AGGREGATE public.agg_tsvector (
158         sfunc    = public.tsvector_concat,
159         basetype = tsvector,
160         stype    = tsvector
161 );
162
163 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
164         BEGIN
165                 RETURN $1::regclass;
166         END;
167 $$ language 'plpgsql';
168
169
170 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
171         SELECT  a.*
172           FROM  connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
173                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
174                 JOIN actor.org_unit a ON a.id = t.keyid
175           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
176 $$ LANGUAGE SQL STABLE;
177
178 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
179         SELECT  a.*
180           FROM  connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
181                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
182                 JOIN actor.org_unit a ON a.id = t.keyid
183           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
184 $$ LANGUAGE SQL STABLE;
185
186 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
187         SELECT  a.*
188           FROM  connectby('actor.org_unit','id','parent_ou','name',
189                                 (SELECT x.id
190                                    FROM actor.org_unit_ancestors($1) x
191                                         JOIN actor.org_unit_type y ON x.ou_type = y.id
192                                   WHERE y.depth = $2)
193                 ,'100','.')
194                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
195                 JOIN actor.org_unit a ON a.id = t.keyid
196           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
197 $$ LANGUAGE SQL STABLE;
198
199 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
200         SELECT  a.*
201           FROM  actor.org_unit a
202           WHERE id = ( SELECT FIRST(x.id)
203                          FROM   actor.org_unit_ancestors($1) x
204                                 JOIN actor.org_unit_type y
205                                         ON x.ou_type = y.id AND y.depth = $2);
206 $$ LANGUAGE SQL STABLE;
207
208 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
209         SELECT  *
210           FROM  actor.org_unit_ancestors($1)
211                         UNION
212         SELECT  *
213           FROM  actor.org_unit_descendants($1);
214 $$ LANGUAGE SQL STABLE;
215
216 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
217         SELECT  * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
218 $$ LANGUAGE SQL STABLE;
219
220 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
221         SELECT  *
222           FROM  actor.org_unit_ancestors($1)
223                         UNION
224         SELECT  *
225           FROM  actor.org_unit_ancestors($2);
226 $$ LANGUAGE SQL STABLE;
227
228 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
229         SELECT  *
230           FROM  actor.org_unit_ancestors($1)
231                         INTERSECT
232         SELECT  *
233           FROM  actor.org_unit_ancestors($2);
234 $$ LANGUAGE SQL STABLE;
235
236 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
237         SELECT COUNT(id)::INT FROM (
238                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
239                         EXCEPT
240                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
241         ) z;
242 $$ LANGUAGE SQL STABLE;
243
244 CREATE AGGREGATE array_accum (
245         sfunc = array_append,
246         basetype = anyelement,
247         stype = anyarray,
248         initcond = '{}'
249 );
250