1 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
13 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
14 ELSE $2::TEXT::INT + 1
17 $$ LANGUAGE SQL STRICT IMMUTABLE;
19 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
23 $txt =~ s/\pM+//go; # Remove diacritics
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
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
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
37 my $commapos = index($txt,',');
39 if ($commapos != length($txt) - 1) {
40 my @list = split /,/, $txt;
41 my $first = shift @list;
42 $txt = $first . ',' . join(' ', @list);
51 $txt =~ s/\s+/ /go; # Compress multiple spaces
52 $txt =~ s/^\s+//o; # Remove leading space
53 $txt =~ s/\s+$//o; # Remove trailing space
56 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
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;
62 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
65 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
67 if (/(\d{3}(?:\.\d+)?)/o) {
70 return (split /\s+/, $txt)[0];
72 $$ LANGUAGE 'plperl' STRICT IMMUTABLE;
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;
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;
82 CREATE AGGREGATE public.first (
83 sfunc = public.first_agg,
84 basetype = anyelement,
88 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
90 $$ LANGUAGE SQL STABLE;
92 CREATE AGGREGATE public.last (
93 sfunc = public.last_agg,
94 basetype = anyelement,
98 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
106 $$ LANGUAGE SQL STABLE;
108 CREATE AGGREGATE public.agg_text (
109 sfunc = public.text_concat,
114 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
122 $$ LANGUAGE SQL STABLE;
124 CREATE AGGREGATE public.agg_tsvector (
125 sfunc = public.tsvector_concat,
130 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
134 $$ language 'plpgsql';
137 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
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;
145 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
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;
153 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
155 FROM connectby('actor.org_unit','id','parent_ou','name',
157 FROM actor.org_unit_ancestors($1) x
158 JOIN actor.org_unit_type y ON x.ou_type = y.id
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;
166 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
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;
175 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
177 FROM actor.org_unit_ancestors($1)
180 FROM actor.org_unit_descendants($1);
181 $$ LANGUAGE SQL STABLE;
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;
187 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
189 FROM actor.org_unit_ancestors($1)
192 FROM actor.org_unit_ancestors($2);
193 $$ LANGUAGE SQL STABLE;
195 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
197 FROM actor.org_unit_ancestors($1)
200 FROM actor.org_unit_ancestors($2);
201 $$ LANGUAGE SQL STABLE;
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)
207 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
209 $$ LANGUAGE SQL STABLE;
211 CREATE AGGREGATE array_accum (
212 sfunc = array_append,
213 basetype = anyelement,