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.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
14 SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
15 $$ LANGUAGE SQL STABLE;
17 CREATE AGGREGATE public.first (
18 sfunc = public.first_agg,
19 basetype = anyelement,
23 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
25 $$ LANGUAGE SQL STABLE;
27 CREATE AGGREGATE public.last (
28 sfunc = public.last_agg,
29 basetype = anyelement,
33 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
41 $$ LANGUAGE SQL STABLE;
43 CREATE AGGREGATE public.agg_text (
44 sfunc = public.text_concat,
49 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
57 $$ LANGUAGE SQL STABLE;
59 CREATE AGGREGATE public.agg_tsvector (
60 sfunc = public.tsvector_concat,
65 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
69 $$ language 'plpgsql';
72 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
74 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
75 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
76 JOIN actor.org_unit a ON a.id = t.keyid
77 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
78 $$ LANGUAGE SQL STABLE;
80 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
82 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
83 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
84 JOIN actor.org_unit a ON a.id = t.keyid
85 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
86 $$ LANGUAGE SQL STABLE;
88 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
90 FROM connectby('actor.org_unit','id','parent_ou','name',
92 FROM actor.org_unit_ancestors($1) x
93 JOIN actor.org_unit_type y ON x.ou_type = y.id
96 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
97 JOIN actor.org_unit a ON a.id = t.keyid
98 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
99 $$ LANGUAGE SQL STABLE;
101 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
103 FROM actor.org_unit_ancestors($1)
106 FROM actor.org_unit_descendants($1);
107 $$ LANGUAGE SQL STABLE;
109 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
111 FROM actor.org_unit_ancestors($1)
114 FROM actor.org_unit_ancestors($2);
115 $$ LANGUAGE SQL STABLE;
117 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
119 FROM actor.org_unit_ancestors($1)
122 FROM actor.org_unit_ancestors($2);
123 $$ LANGUAGE SQL STABLE;
125 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
126 SELECT COUNT(id)::INT FROM (
127 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
129 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
131 $$ LANGUAGE SQL STABLE;
133 CREATE AGGREGATE array_accum (
134 sfunc = array_append,
135 basetype = anyelement,