1 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
4 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
6 if (/(\d{3}(?:\.\d+)?)/o) {
9 return (split /\s+/, $txt)[0];
11 $$ LANGUAGE 'plperl' STRICT IMMUTABLE;
13 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
14 SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
15 $$ LANGUAGE SQL STRICT IMMUTABLE;
17 CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
18 SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
19 $$ LANGUAGE SQL STABLE;
21 CREATE AGGREGATE public.first (
22 sfunc = public.first_agg,
23 basetype = anyelement,
27 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
29 $$ LANGUAGE SQL STABLE;
31 CREATE AGGREGATE public.last (
32 sfunc = public.last_agg,
33 basetype = anyelement,
37 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
45 $$ LANGUAGE SQL STABLE;
47 CREATE AGGREGATE public.agg_text (
48 sfunc = public.text_concat,
53 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
61 $$ LANGUAGE SQL STABLE;
63 CREATE AGGREGATE public.agg_tsvector (
64 sfunc = public.tsvector_concat,
69 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
73 $$ language 'plpgsql';
76 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
78 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
79 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
80 JOIN actor.org_unit a ON a.id = t.keyid
81 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
82 $$ LANGUAGE SQL STABLE;
84 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
86 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
87 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
88 JOIN actor.org_unit a ON a.id = t.keyid
89 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
90 $$ LANGUAGE SQL STABLE;
92 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
94 FROM connectby('actor.org_unit','id','parent_ou','name',
96 FROM actor.org_unit_ancestors($1) x
97 JOIN actor.org_unit_type y ON x.ou_type = y.id
100 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
101 JOIN actor.org_unit a ON a.id = t.keyid
102 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
103 $$ LANGUAGE SQL STABLE;
105 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
107 FROM actor.org_unit a
108 WHERE id = ( SELECT FIRST(x.id)
109 FROM actor.org_unit_ancestors($1) x
110 JOIN actor.org_unit_type y
111 ON x.ou_type = y.id AND y.depth = $2);
112 $$ LANGUAGE SQL STABLE;
114 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
116 FROM actor.org_unit_ancestors($1)
119 FROM actor.org_unit_descendants($1);
120 $$ LANGUAGE SQL STABLE;
122 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
123 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
124 $$ LANGUAGE SQL STABLE;
126 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
128 FROM actor.org_unit_ancestors($1)
131 FROM actor.org_unit_ancestors($2);
132 $$ LANGUAGE SQL STABLE;
134 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
136 FROM actor.org_unit_ancestors($1)
139 FROM actor.org_unit_ancestors($2);
140 $$ LANGUAGE SQL STABLE;
142 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
143 SELECT COUNT(id)::INT FROM (
144 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
146 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
148 $$ LANGUAGE SQL STABLE;
150 CREATE AGGREGATE array_accum (
151 sfunc = array_append,
152 basetype = anyelement,