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