1 CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
2 SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
3 $$ LANGUAGE SQL STABLE;
5 CREATE AGGREGATE public.first (
6 sfunc = public.first_agg,
11 CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
13 $$ LANGUAGE SQL STABLE;
15 CREATE AGGREGATE public.last (
16 sfunc = public.last_agg,
17 basetype = anyelement,
21 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
29 $$ LANGUAGE SQL STABLE;
31 CREATE AGGREGATE public.agg_text (
32 sfunc = public.text_concat,
37 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
45 $$ LANGUAGE SQL STABLE;
47 CREATE AGGREGATE public.agg_tsvector (
48 sfunc = public.tsvector_concat,
53 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
57 $$ language 'plpgsql';
60 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
62 FROM connectby('actor.org_unit','id','parent_ou','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_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
70 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
71 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
72 JOIN actor.org_unit a ON a.id = t.keyid
73 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
74 $$ LANGUAGE SQL STABLE;
76 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
78 FROM connectby('actor.org_unit','id','parent_ou','name',
80 FROM actor.org_unit_ancestors($1) x
81 JOIN actor.org_unit_type y ON x.ou_type = y.id
84 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
85 JOIN actor.org_unit a ON a.id = t.keyid
86 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
87 $$ LANGUAGE SQL STABLE;
89 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
92 WHERE id = ( SELECT FIRST(x.id)
93 FROM actor.org_unit_ancestors($1) x
94 JOIN actor.org_unit_type y
95 ON x.ou_type = y.id AND y.depth = $2);
96 $$ LANGUAGE SQL STABLE;
98 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
100 FROM actor.org_unit_ancestors($1)
103 FROM actor.org_unit_descendants($1);
104 $$ LANGUAGE SQL STABLE;
106 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
107 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
108 $$ LANGUAGE SQL STABLE;
110 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
112 FROM actor.org_unit_ancestors($1)
115 FROM actor.org_unit_ancestors($2);
116 $$ LANGUAGE SQL STABLE;
118 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
120 FROM actor.org_unit_ancestors($1)
123 FROM actor.org_unit_ancestors($2);
124 $$ LANGUAGE SQL STABLE;
126 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
127 SELECT COUNT(id)::INT FROM (
128 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
130 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
132 $$ LANGUAGE SQL STABLE;
134 CREATE AGGREGATE array_accum (
135 sfunc = array_append,
136 basetype = anyelement,