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.call_number_dewey( TEXT ) RETURNS TEXT AS $$
22 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
24 if (/(\d{3}(?:\.\d+)?)/o) {
27 return (split /\s+/, $txt)[0];
29 $$ LANGUAGE 'plperl' STRICT IMMUTABLE;
31 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
32 SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
33 $$ LANGUAGE SQL STRICT IMMUTABLE;
35 CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
36 SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
37 $$ LANGUAGE SQL STABLE;
39 CREATE AGGREGATE public.first (
40 sfunc = public.first_agg,
41 basetype = anyelement,
45 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
47 $$ LANGUAGE SQL STABLE;
49 CREATE AGGREGATE public.last (
50 sfunc = public.last_agg,
51 basetype = anyelement,
55 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
63 $$ LANGUAGE SQL STABLE;
65 CREATE AGGREGATE public.agg_text (
66 sfunc = public.text_concat,
71 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
79 $$ LANGUAGE SQL STABLE;
81 CREATE AGGREGATE public.agg_tsvector (
82 sfunc = public.tsvector_concat,
87 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
91 $$ language 'plpgsql';
94 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
96 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
97 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
98 JOIN actor.org_unit a ON a.id = t.keyid
99 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
100 $$ LANGUAGE SQL STABLE;
102 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
104 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
105 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
106 JOIN actor.org_unit a ON a.id = t.keyid
107 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
108 $$ LANGUAGE SQL STABLE;
110 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
112 FROM connectby('actor.org_unit','id','parent_ou','name',
114 FROM actor.org_unit_ancestors($1) x
115 JOIN actor.org_unit_type y ON x.ou_type = y.id
118 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
119 JOIN actor.org_unit a ON a.id = t.keyid
120 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
121 $$ LANGUAGE SQL STABLE;
123 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
125 FROM actor.org_unit a
126 WHERE id = ( SELECT FIRST(x.id)
127 FROM actor.org_unit_ancestors($1) x
128 JOIN actor.org_unit_type y
129 ON x.ou_type = y.id AND y.depth = $2);
130 $$ LANGUAGE SQL STABLE;
132 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
134 FROM actor.org_unit_ancestors($1)
137 FROM actor.org_unit_descendants($1);
138 $$ LANGUAGE SQL STABLE;
140 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
141 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
142 $$ LANGUAGE SQL STABLE;
144 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
146 FROM actor.org_unit_ancestors($1)
149 FROM actor.org_unit_ancestors($2);
150 $$ LANGUAGE SQL STABLE;
152 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
154 FROM actor.org_unit_ancestors($1)
157 FROM actor.org_unit_ancestors($2);
158 $$ LANGUAGE SQL STABLE;
160 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
161 SELECT COUNT(id)::INT FROM (
162 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
164 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
166 $$ LANGUAGE SQL STABLE;
168 CREATE AGGREGATE array_accum (
169 sfunc = array_append,
170 basetype = anyelement,