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 ($txt =~ /(\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