4 CREATE TABLE config.floating_group (
6 name TEXT UNIQUE NOT NULL,
7 manual BOOL NOT NULL DEFAULT FALSE
10 CREATE TABLE config.floating_group_member (
11 id SERIAL PRIMARY KEY,
12 floating_group INT NOT NULL REFERENCES config.floating_group (id),
13 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
14 stop_depth INT NOT NULL DEFAULT 0,
16 exclude BOOL NOT NULL DEFAULT FALSE
19 CREATE OR REPLACE FUNCTION evergreen.can_float( copy_floating_group integer, from_ou integer, to_ou integer ) RETURNS BOOL AS $f$
21 float_member config.floating_group_member%ROWTYPE;
25 -- Grab the shared OU depth. If this is less than the stop depth later we ignore the entry.
26 SELECT INTO shared_ou_depth max(depth) FROM actor.org_unit_common_ancestors( from_ou, to_ou ) aou JOIN actor.org_unit_type aout ON aou.ou_type = aout.id;
27 -- Grab the to ou depth. If this is greater than max depth we ignore the entry.
28 SELECT INTO to_ou_depth depth FROM actor.org_unit aou JOIN actor.org_unit_type aout ON aou.ou_type = aout.id WHERE aou.id = to_ou;
29 -- Grab float members that apply. We don't care what we get beyond wanting excluded ones first.
30 SELECT INTO float_member *
32 config.floating_group_member cfgm
33 JOIN actor.org_unit aou ON cfgm.org_unit = aou.id
34 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
36 cfgm.floating_group = copy_floating_group
37 AND to_ou IN (SELECT id FROM actor.org_unit_descendants(aou.id))
38 AND cfgm.stop_depth <= shared_ou_depth
39 AND (cfgm.max_depth IS NULL OR to_ou_depth <= max_depth)
42 -- If we found something then we want to return the opposite of the exclude flag
44 RETURN NOT float_member.exclude;
46 -- Otherwise no floating.