1 CREATE OR REPLACE FUNCTION asset.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.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
21 $$ LANGUAGE SQL STABLE;
23 CREATE AGGREGATE public.agg_text (
24 sfunc = public.text_concat,
29 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
37 $$ LANGUAGE SQL STABLE;
39 CREATE AGGREGATE public.agg_tsvector (
40 sfunc = public.tsvector_concat,
45 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
49 $$ language 'plpgsql';
52 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
54 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
55 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
56 JOIN actor.org_unit a ON a.id = t.keyid
57 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
58 $$ LANGUAGE SQL STABLE;
60 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
62 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
63 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
64 JOIN actor.org_unit a ON a.id = t.keyid
65 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
66 $$ LANGUAGE SQL STABLE;
68 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
70 FROM connectby('actor.org_unit','id','parent_ou','name',
72 FROM actor.org_unit_ancestors($1) x
73 JOIN actor.org_unit_type y ON x.ou_type = y.id
76 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
77 JOIN actor.org_unit a ON a.id = t.keyid
78 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
79 $$ LANGUAGE SQL STABLE;
81 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
83 FROM actor.org_unit_ancestors($1)
86 FROM actor.org_unit_descendants($1);
87 $$ LANGUAGE SQL STABLE;
89 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
91 FROM actor.org_unit_ancestors($1)
94 FROM actor.org_unit_ancestors($2);
95 $$ LANGUAGE SQL STABLE;
97 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
99 FROM actor.org_unit_ancestors($1)
102 FROM actor.org_unit_ancestors($2);
103 $$ LANGUAGE SQL STABLE;
105 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
106 SELECT COUNT(id)::INT FROM (
107 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
109 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
111 $$ LANGUAGE SQL STABLE;
113 CREATE AGGREGATE array_accum (
114 sfunc = array_append,
115 basetype = anyelement,