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/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
 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 public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
 129         SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
 130 $$ LANGUAGE SQL STABLE;
 131
 132 CREATE AGGREGATE public.first (
 133         sfunc    = public.first_agg,
 134         basetype = anyelement,
 135         stype    = anyelement
 136 );
 137
 138 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
 139         SELECT $2;
 140 $$ LANGUAGE SQL STABLE;
 141
 142 CREATE AGGREGATE public.last (
 143         sfunc    = public.last_agg,
 144         basetype = anyelement,
 145         stype    = anyelement
 146 );
 147
 148 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
 149 SELECT
 150         CASE    WHEN $1 IS NULL
 151                         THEN $2
 152                 WHEN $2 IS NULL
 153                         THEN $1
 154                 ELSE $1 || ' ' || $2
 155         END;
 156 $$ LANGUAGE SQL STABLE;
 157
 158 CREATE AGGREGATE public.agg_text (
 159         sfunc    = public.text_concat,
 160         basetype = text,
 161         stype    = text
 162 );
 163
 164 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
 165 SELECT
 166         CASE    WHEN $1 IS NULL
 167                         THEN $2
 168                 WHEN $2 IS NULL
 169                         THEN $1
 170                 ELSE $1 || ' ' || $2
 171         END;
 172 $$ LANGUAGE SQL STABLE;
 173
 174 CREATE AGGREGATE public.agg_tsvector (
 175         sfunc    = public.tsvector_concat,
 176         basetype = tsvector,
 177         stype    = tsvector
 178 );
 179
 180 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
 181         BEGIN
 182                 RETURN $1::regclass;
 183         END;
 184 $$ language 'plpgsql';
 185
 186
 187 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
 188         SELECT  a.*
 189           FROM  connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
 190                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 191                 JOIN actor.org_unit a ON a.id = t.keyid
 192           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
 193 $$ LANGUAGE SQL STABLE;
 194
 195 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
 196         SELECT  a.*
 197           FROM  connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
 198                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 199                 JOIN actor.org_unit a ON a.id = t.keyid
 200           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
 201 $$ LANGUAGE SQL STABLE;
 202
 203 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
 204         SELECT  a.*
 205           FROM  connectby('actor.org_unit','id','parent_ou','name',
 206                                 (SELECT x.id
 207                                    FROM actor.org_unit_ancestors($1) x
 208                                         JOIN actor.org_unit_type y ON x.ou_type = y.id
 209                                   WHERE y.depth = $2)
 210                 ,'100','.')
 211                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 212                 JOIN actor.org_unit a ON a.id = t.keyid
 213           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
 214 $$ LANGUAGE SQL STABLE;
 215
 216 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
 217         SELECT  a.*
 218           FROM  actor.org_unit a
 219           WHERE id = ( SELECT FIRST(x.id)
 220                          FROM   actor.org_unit_ancestors($1) x
 221                                 JOIN actor.org_unit_type y
 222                                         ON x.ou_type = y.id AND y.depth = $2);
 223 $$ LANGUAGE SQL STABLE;
 224
 225 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
 226         SELECT  *
 227           FROM  actor.org_unit_ancestors($1)
 228                         UNION
 229         SELECT  *
 230           FROM  actor.org_unit_descendants($1);
 231 $$ LANGUAGE SQL STABLE;
 232
 233 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 234         SELECT  * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
 235 $$ LANGUAGE SQL STABLE;
 236
 237 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 238         SELECT  *
 239           FROM  actor.org_unit_ancestors($1)
 240                         UNION
 241         SELECT  *
 242           FROM  actor.org_unit_ancestors($2);
 243 $$ LANGUAGE SQL STABLE;
 244
 245 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 246         SELECT  *
 247           FROM  actor.org_unit_ancestors($1)
 248                         INTERSECT
 249         SELECT  *
 250           FROM  actor.org_unit_ancestors($2);
 251 $$ LANGUAGE SQL STABLE;
 252
 253 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
 254         SELECT COUNT(id)::INT FROM (
 255                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
 256                         EXCEPT
 257                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
 258         ) z;
 259 $$ LANGUAGE SQL STABLE;
 260
 261 CREATE AGGREGATE array_accum (
 262         sfunc = array_append,
 263         basetype = anyelement,
 264         stype = anyarray,
 265         initcond = '{}'
 266 );
 267