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_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
107 FROM actor.org_unit_ancestors($1)
110 FROM actor.org_unit_descendants($1);
111 $$ LANGUAGE SQL STABLE;
113 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
115 FROM actor.org_unit_ancestors($1)
118 FROM actor.org_unit_ancestors($2);
119 $$ LANGUAGE SQL STABLE;
121 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
123 FROM actor.org_unit_ancestors($1)
126 FROM actor.org_unit_ancestors($2);
127 $$ LANGUAGE SQL STABLE;
129 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
130 SELECT COUNT(id)::INT FROM (
131 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
133 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
135 $$ LANGUAGE SQL STABLE;
137 CREATE AGGREGATE array_accum (
138 sfunc = array_append,
139 basetype = anyelement,