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