More preparation for PostgreSQL 8.3 support:
[Evergreen.git] / Open-ILS / src / sql / Pg / 020.schema.functions.sql
1 /*
2  * Copyright (C) 2004-2008  Georgia Public Library Service
3  * Copyright (C) 2007-2008  Equinox Software, Inc.
4  * Mike Rylander <miker@esilibrary.com> 
5  *
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.
10  *
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.
15  *
16  */
17
18 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
19         SELECT  SUBSTRING(
20                         REGEXP_REPLACE(
21                                 REGEXP_REPLACE(
22                                         $1,
23                                         E'\W*$',
24                                         ''
25                                 ),
26                                 '  ',
27                                 ' '
28                         ),
29                         CASE
30                                 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
31                                 ELSE $2::TEXT::INT + 1
32                         END
33                 );
34 $$ LANGUAGE SQL STRICT IMMUTABLE;
35
36 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
37     use Unicode::Normalize;
38
39         my $txt = lc(shift);
40         my $sf = shift;
41
42     $txt = NFD($txt);
43         $txt =~ s/\pM+//go;     # Remove diacritics
44
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
48
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
51
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
55
56         if ($sf && $sf =~ /^a/o) {
57                 my $commapos = index($txt,',');
58                 if ($commapos > -1) {
59                         if ($commapos != length($txt) - 1) {
60                                 my @list = split /,/, $txt;
61                                 my $first = shift @list;
62                                 $txt = $first . ',' . join(' ', @list);
63                         } else {
64                                 $txt =~ s/,/ /go;
65                         }
66                 }
67         } else {
68                 $txt =~ s/,/ /go;
69         }
70
71         $txt =~ s/\s+/ /go;     # Compress multiple spaces
72         $txt =~ s/^\s+//o;      # Remove leading space
73         $txt =~ s/\s+$//o;      # Remove trailing space
74
75         return $txt;
76 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
77
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;
81
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');
84 $$ LANGUAGE SQL;
85
86 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
87     return lc(shift);
88 $$ LANGUAGE PLPERLU;
89
90 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
91     return uc(shift);
92 $$ LANGUAGE PLPERLU;
93
94 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
95     use Unicode::Normalize;
96
97     my $x = NFD(shift);
98     $x =~ s/\pM+//go;
99     return $x;
100
101 $$ LANGUAGE PLPERLU;
102
103 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
104     use Unicode::Normalize;
105
106     my $x = NFC(shift);
107     $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
108     return $x;
109
110 $$ LANGUAGE PLPERLU;
111
112 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
113         my $txt = shift;
114         $txt =~ s/^\s+//o;
115         $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
116         $txt =~ s/\s+$//o;
117         if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
118                 return $1;
119         } else {
120                 return (split /\s+/, $txt)[0];
121         }
122 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
123
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;
127
128 CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
129         BEGIN
130                 RETURN $1::regclass;
131         END;
132 $$ language 'plpgsql';
133
134
135 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
136         SELECT  a.*
137           FROM  connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,$1::text,100,'.'::text)
138                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
139                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
140           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
141 $$ LANGUAGE SQL STABLE;
142
143 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
144         SELECT  a.*
145           FROM  connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
146                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
147                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
148           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
149 $$ LANGUAGE SQL STABLE;
150
151 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
152         SELECT  a.*
153           FROM  connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,
154                                 (SELECT x.id
155                                    FROM actor.org_unit_ancestors($1) x
156                                         JOIN actor.org_unit_type y ON x.ou_type = y.id
157                                   WHERE y.depth = $2)::text
158                 ,100,'.'::text)
159                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
160                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
161           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
162 $$ LANGUAGE SQL STABLE;
163
164 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
165         SELECT  a.*
166           FROM  actor.org_unit a
167           WHERE id = ( SELECT FIRST(x.id)
168                          FROM   actor.org_unit_ancestors($1) x
169                                 JOIN actor.org_unit_type y
170                                         ON x.ou_type = y.id AND y.depth = $2);
171 $$ LANGUAGE SQL STABLE;
172
173 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
174         SELECT  *
175           FROM  actor.org_unit_ancestors($1)
176                         UNION
177         SELECT  *
178           FROM  actor.org_unit_descendants($1);
179 $$ LANGUAGE SQL STABLE;
180
181 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
182         SELECT  * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
183 $$ LANGUAGE SQL STABLE;
184
185 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
186         SELECT  *
187           FROM  actor.org_unit_ancestors($1)
188                         UNION
189         SELECT  *
190           FROM  actor.org_unit_ancestors($2);
191 $$ LANGUAGE SQL STABLE;
192
193 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
194         SELECT  *
195           FROM  actor.org_unit_ancestors($1)
196                         INTERSECT
197         SELECT  *
198           FROM  actor.org_unit_ancestors($2);
199 $$ LANGUAGE SQL STABLE;
200
201 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
202         SELECT COUNT(id)::INT FROM (
203                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
204                         EXCEPT
205                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
206         ) z;
207 $$ LANGUAGE SQL STABLE;
208
209