first() and last() aggregates
[Evergreen.git] / Open-ILS / src / sql / Pg / 020.schema.functions.sql
1 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
2         my $txt = shift;
3         $txt =~ s/^\s+//o;
4         $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//o;
5         $txt =~ s/\s+$//o;
6         if (/(\d{3}(?:\.\d+)?)/o) {
7                 return $1;
8         } else {
9                 return (split /\s+/, $txt)[0];
10         }
11 $$ LANGUAGE 'plperl' STRICT IMMUTABLE;
12
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;
16
17 CREATE AGGREGATE public.first (
18         sfunc    = public.first_agg,
19         basetype = anyelement,
20         stype    = anyelement
21 );
22
23 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
24         SELECT $2;
25 $$ LANGUAGE SQL STABLE;
26
27 CREATE AGGREGATE public.last (
28         sfunc    = public.last_agg,
29         basetype = anyelement,
30         stype    = anyelement
31 );
32
33 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
34 SELECT
35         CASE    WHEN $1 IS NULL
36                         THEN $2
37                 WHEN $2 IS NULL
38                         THEN $1
39                 ELSE $1 || ' ' || $2
40         END;
41 $$ LANGUAGE SQL STABLE;
42
43 CREATE AGGREGATE public.agg_text (
44         sfunc    = public.text_concat,
45         basetype = text,
46         stype    = text
47 );
48
49 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
50 SELECT
51         CASE    WHEN $1 IS NULL
52                         THEN $2
53                 WHEN $2 IS NULL
54                         THEN $1
55                 ELSE $1 || ' ' || $2
56         END;
57 $$ LANGUAGE SQL STABLE;
58
59 CREATE AGGREGATE public.agg_tsvector (
60         sfunc    = public.tsvector_concat,
61         basetype = tsvector,
62         stype    = tsvector
63 );
64
65 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
66         BEGIN
67                 RETURN $1::regclass;
68         END;
69 $$ language 'plpgsql';
70
71
72 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
73         SELECT  a.*
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;
79
80 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
81         SELECT  a.*
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;
87
88 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
89         SELECT  a.*
90           FROM  connectby('actor.org_unit','id','parent_ou','name',
91                                 (SELECT x.id
92                                    FROM actor.org_unit_ancestors($1) x
93                                         JOIN actor.org_unit_type y ON x.ou_type = y.id
94                                   WHERE y.depth = $2)
95                 ,'100','.')
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;
100
101 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
102         SELECT  *
103           FROM  actor.org_unit_ancestors($1)
104                         UNION
105         SELECT  *
106           FROM  actor.org_unit_descendants($1);
107 $$ LANGUAGE SQL STABLE;
108
109 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
110         SELECT  *
111           FROM  actor.org_unit_ancestors($1)
112                         UNION
113         SELECT  *
114           FROM  actor.org_unit_ancestors($2);
115 $$ LANGUAGE SQL STABLE;
116
117 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
118         SELECT  *
119           FROM  actor.org_unit_ancestors($1)
120                         INTERSECT
121         SELECT  *
122           FROM  actor.org_unit_ancestors($2);
123 $$ LANGUAGE SQL STABLE;
124
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)
128                         EXCEPT
129                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
130         ) z;
131 $$ LANGUAGE SQL STABLE;
132
133 CREATE AGGREGATE array_accum (
134         sfunc = array_append,
135         basetype = anyelement,
136         stype = anyarray,
137         initcond = '{}'
138 );