1 CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$
9 $$ LANGUAGE SQL STABLE;
11 CREATE AGGREGATE public.agg_text (
12 sfunc = public.text_concat,
17 CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$
25 $$ LANGUAGE SQL STABLE;
27 CREATE AGGREGATE public.agg_tsvector (
28 sfunc = public.tsvector_concat,
33 CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
37 $$ language 'plpgsql';
40 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
42 FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.')
43 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
44 JOIN actor.org_unit a ON a.id = t.keyid
45 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
46 $$ LANGUAGE SQL STABLE;
48 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
50 FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.')
51 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
52 JOIN actor.org_unit a ON a.id = t.keyid
53 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
54 $$ LANGUAGE SQL STABLE;
56 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
58 FROM connectby('actor.org_unit','id','parent_ou','name',
60 FROM actor.org_unit_ancestors($1) x
61 JOIN actor.org_unit_type y ON x.ou_type = y.id
64 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
65 JOIN actor.org_unit a ON a.id = t.keyid
66 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
67 $$ LANGUAGE SQL STABLE;
69 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
71 FROM actor.org_unit_ancestors($1)
74 FROM actor.org_unit_descendants($1);
75 $$ LANGUAGE SQL STABLE;
77 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
79 FROM actor.org_unit_ancestors($1)
82 FROM actor.org_unit_ancestors($2);
83 $$ LANGUAGE SQL STABLE;
85 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
87 FROM actor.org_unit_ancestors($1)
90 FROM actor.org_unit_ancestors($2);
91 $$ LANGUAGE SQL STABLE;
93 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
94 SELECT COUNT(id)::INT FROM (
95 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
97 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
99 $$ LANGUAGE SQL STABLE;