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;
58 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
60 FROM connectby('actor.org_unit','id','parent_ou','name',
62 FROM actor.org_unit_ancestors($1) x
63 JOIN actor.org_unit_type y ON x.ou_type = y.id
66 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
67 JOIN actor.org_unit a ON a.id = t.keyid
68 ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
69 $$ LANGUAGE SQL STABLE;
71 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS '
73 FROM actor.org_unit_ancestors($1)
76 FROM actor.org_unit_descendants($1);
77 ' LANGUAGE SQL STABLE;
80 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS '
81 SELECT COUNT(id)::INT FROM (
82 select * from (SELECT id FROM actor.org_unit_ancestors($1) UNION SELECT id FROM actor.org_unit_ancestors($2)) x
84 select * from (SELECT id FROM actor.org_unit_ancestors($1) INTERSECT SELECT id FROM actor.org_unit_ancestors($2)) y) z;
85 ' LANGUAGE SQL STABLE;